Splitting Strings in SQL
Let’s say that you have a column that contains some goofy data and it looks like this:
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.
This is Jeremiah
I live in Portland, OR. I have two dogs.
I recently received a Master's of Science in Computer Science from Portland State University.
I'm was Microsoft MVP from 2009 - 2018 with a pile of certifications. Somewhere along the way, I wrote a database client for Riak and then handed it off to the community.