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;