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.
