Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL split based on delimeter, keep only second element

I have T-SQL code and am researching how to split

Aruba\abc
Spain\defg
New Zealand\qwerty
Antartica\sporty

Such that the column outputs

abc
defg
qwerty
sporty

So far, I found something like this,

http://www.aspsnippets.com/Articles/Split-function-in-SQL-Server-Example-Function-to-Split-Comma-separated-Delimited-string-in-SQL-Server-2005-2008-and-2012.aspx

But it splits column based on delimiters into new columns.

I wish to keep the information AFTER the delimiter \

Please advise

like image 431
Rhonda Avatar asked Aug 11 '15 19:08

Rhonda


2 Answers

SELECT RIGHT(ColName , LEN(ColName) - CHARINDEX('\', ColName) )
FROM TABLEName

OR

SELECT PARSENAME(REPLACE(ColName , '\' , '.'),1)
FROM TableName
like image 116
M.Ali Avatar answered Sep 28 '22 01:09

M.Ali


If you have it as a variable example:

DECLARE @str VARCHAR(50) = 'aruba\abc'

SELECT SUBSTRING(@str,CHARINDEX('\', @str)+1, LEN(@str) - CHARINDEX('\', @str) )

If you have it in a table example:

SELECT SUBSTRING(column1,CHARINDEX('\', column1)+1, LEN(column1) - CHARINDEX('\', column1) )
FROM table1

Here's a sqlfiddle of it working : http://sqlfiddle.com/#!6/85de5/1

like image 34
Avitus Avatar answered Sep 28 '22 01:09

Avitus