CODEWITHSUNDEEP
sqlsql-server
Adam Shlomi
Adam Shlomi

Reputation: 21

Split a Column in SQL Server

I'm trying to split a column in SQL. There is an Address, then a series of spaces (which vary between row) and either a 4 or 5 character date (ex. 11/04 or 3/15) and then another large set of spaces.

I want to grab the dates but not have them get cut off. My big problem now is that the last digit of the zip code will enter into the date or we will cut off the first part of the date depending on which method we use 

,CASE WHEN Addresses ='Missing' THEN 'Missing'  ELSE  RIGHT(replace(Addresses2,space(1),''),6) END AS EmploymentDate
,CASE WHEN Addresses ='Missing' THEN 'Missing'  ELSE  RIGHT(replace(Addresses2,space(1),''),7) END AS EmploymentDate2

Visualization of the problem here

Upvotes: 2

Views: 69

Answers (2)

Vitaly Borisov
Vitaly Borisov

Reputation: 1193

Trim spaces from right, then take the date, then trim spaces from left:

SELECT t.Addresses2,LTRIM(RIGHT(RTRIM(t.Addresses2),5)) AS [EmploymentDate]
FROM [YourTableName] t
;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You could use patindex(). Assuming there are at least three spaces before the date:

select trim(substring(address, patindex('%   [0-9]%', address) + 3, 5))

Upvotes: 1

Related Questions