Splitting Strings in SQL

Let’s say that you have a column that contains some goofy data and it looks like this:

USA/Ohio
USA/Indiana
USA/Iowa
UK/Scotland
UK/England

So, how would you typically split such a string to only get the part after the ‘/’?

Well, here’s how I did it:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
  DROP TABLE #tmp;
GO

CREATE TABLE #tmp
(
  s VARCHAR(255)
);
GO

INSERT INTO #tmp (s) VALUES
('USA/Ohio'),
('USA/Indiana'),
('USA/Iowa'),
('UK/Scotland'),
('UK/England');

SELECT SUBSTRING(s, CHARINDEX('/', s) + 1, LEN(s)) -- this is what does all the work
FROM #tmp;

But, apparently, there’s a simpler way:

SELECT PARSENAME(REPLACE(a, '/', '.'),1) 
FROM #tmp;

It’s a bit hacky, but it works.

Basically, it takes advantage of the PARSENAME function to grab specific sections of a period delimited string, as it it were a SQL Server object.

Now you know. Paypal me some money if you find this useful and pass it off as your own work of genius.

Menu