Can't find a proper function to separate my strings.
I tried LEFT and RIGHT function but only applies to one value not multiple, but using the SPLIT_STRING function, it separates all value but doesnot correlate with the values it separated from. i.e i need the Atlanta in a separate column and the IN in another column. (Location) Atlanta, IN Atlanta, IN Cedar Rapids, IA Cedar Rapids, IA Indianapolis, IN Indianapolis, IN Dearborn, MI
SELECT * FROM Salary_2 CROSS APPLY string_split ("Location", ',')
(value) Atlanta IN Cedar Rapids IA Indianapolis IN Dearborn MI
CodePudding user response：
You can do it using SUBSTRING and CHARINDEX
SELECT location, SUBSTRING(location, 0, CHARINDEX(',', location)) as city,
SUBSTRING(location, CHARINDEX(',', location) 2, 4) as code
FROM Salary_2
SUBSTRING to extracts some characters from a string.
CHARINDEX to searches for a substring in a string, and returns the position.
check it here : https://dbfiddle.uk/pcmBIH68
CodePudding user response：
A numbers makes it pretty quick. This has the advantage of not requiring an explicit loop via a script. It also lets you capture the order of the results if that were important.
declare @s varchar(255) = 'Atlanta, IN Cedar Rapids, IA Indianapolis, IN Dearborn, MI';
with data as (
select substring(@s,
5 lag(n, 1, -4) over (order by n),
n - lag(n, 1, -4) over (order by n)
) as CityState, n
from numbers
where substring(@s, n, 1) = ','
)
select n, left(CityState, len(CityState) - 4), right(CityState, 2)
from data
order by n;