CODEWITHSUNDEEP
sql-serversql-server-2008
Nemo
Nemo

Reputation: 1111

Parse characters in SQL Server 2008

I have a SQL column with name txt_Full_Path which has data as given below

E:/My_Users//kpa1/eOReport - VSD.mrk 
E:/My_Users//krishr9/Report.mrk 
E:/My_Users//kristc/AllPAS.mrk 
E:/My_Users//kukerm/v1.mrk      
E:/My_Users//ksnedike/abcdef.mrk

how do I get the value after first occurrence of '//' moving from left to right?

so that I get these values in a new column.

kpa1
krishr9
kristc
kukerm
ksnedike

Upvotes: 2

Views: 1011

Answers (2)

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32720

It's ugly, but it works:

SELECT SUBSTRING(YourValue, CHARINDEX('//', YourValue) + 2, 
    ABS(CHARINDEX('/', YourValue, CHARINDEX('//', YourValue) + 2) - 
        (CHARINDEX('//', YourValue) + 2)))    
FROM YourTable

EDIT

Added the ABS() function in the last portion to handle folders without a / after the //.

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280490

DECLARE @x TABLE(y VARCHAR(64))

INSERT @x SELECT 'E:/My_Users//kpa1/eOReport - VSD.mrk' 
UNION ALL SELECT 'E:/My_Users//krishr9/Report.mrk' 
UNION ALL SELECT 'E:/My_Users//kristc/AllPAS.mrk' 
UNION ALL SELECT 'E:/My_Users//kukerm/v1.mrk'
UNION ALL SELECT 'E:/My_Users//ksnedike/abcdef.mrk';

SELECT y, part = SUBSTRING(y, 2, CHARINDEX('/', y, 2)-2)
FROM (SELECT y = SUBSTRING(y, CHARINDEX('//', y) + 1, 64) FROM @x) AS z;

Results:

y                         part
------------------------  ----------
/kpa1/eOReport - VSD.mrk  kpa1
/krishr9/Report.mrk       krishr9
/kristc/AllPAS.mrk        kristc
/kukerm/v1.mrk            kukerm
/ksnedike/abcdef.mrk      ksnedike

You may also run into a case where there is no trailing subsequent / character, e.g. 'E:/foo//bar' - if so then:

SELECT y, part = SUBSTRING(y, 2, COALESCE(NULLIF(CHARINDEX('/', y, 2),0),66)-2)
FROM (SELECT y = SUBSTRING(y, CHARINDEX('//', y) + 1, 64) FROM @x) AS z;

Upvotes: 3

Related Questions