question

psmittyy14 avatar image
psmittyy14 asked

Parsing out a delimited string into 4 columns

So my script works as follows. select LTRIM(RTRIM(SUBSTRING(value, 1,charindex('_', value)-1))) as Department, LTRIM(RTRIM(SUBSTRING(SUBSTRING(value, Charindex('_', value)+1, LEN(value)), 1,charindex('_', SUBSTRING(value, Charindex('_', value)+1, LEN(value)))-1))) as [user], Ltrim(Rtrim(RIGHT(value, (CHARINDEX('_',REVERSE(value),0))-1))) as email From sslistvalues where listid = 4 my string is like this department_user_title_email for some reason i cannot get it to work in order to get the title column. any help would be greatly appreciated! I am doing it this way because it is for a view and the function did not work.
scriptviewsubstringparsingcharindex
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
Can you also post some sample data?
0 Likes 0 ·
psmittyy14 avatar image psmittyy14 commented ·
Comment below in answers. Allowed me to post pictures of the data as well.
0 Likes 0 ·
Mister Magoo avatar image
Mister Magoo answered
Alternative method that I find a lot simpler to read/understand. select IID, listid, value, b.[department], c.[user], d.[title], e.[email] from @YourTable cross apply (select charindex('_',Value)) AS a(p1) cross apply (select substring( Value, 1 , p1-1 ),charindex('_',Value,p1+1)) AS b([department],p2) cross apply (select substring( Value, p1+1, p2-p1-1 ),charindex('_',Value,p2+1)) AS c([user],p3) cross apply (select substring( Value, p2+1, p3-p2-1 ),charindex('_',Value,p3+1)) AS d([title],p4) cross apply (select substring( Value, p3+1, 1000 ) ) AS e([email])
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
As you have found out string manipulation in TSQL is not pretty! Here's a alternative approach that uses a tally table declare @YourString varchar(100) set @YourString = 'department_user_title_email' set @YourString ='_'+@YourString+'_' ;with cte_tally as ( select top 10000 ROW_NUMBER() over(order by sc1.object_id) N from sys.columns sc1, sys.columns sc2 ) , cte_split as ( select row_number()over(order by N) as rn, substring(@YourString,N+1,charindex('_',@YourString,N+1)-N-1) as SplitString from cte_tally where N < len(@YourString) AND substring(@YourString,N,1) = '_' ) select max(case when rn = 1 then SplitString end) as [department], max(case when rn = 2 then SplitString end) as [user], max(case when rn = 3 then SplitString end) as [title], max(case when rn = 4 then SplitString end) as [email] from cte_split and this returns department user title email ----------- ----- ------- ------ department user title email ------------------- **Edit**: Update to show how to use against a table rather than a variable declare @YourTable table (IID int, ListID int, Value varchar(1000) ) insert into @YourTable select 2,1,'NH- Sales_John Lu_Branch Manager- NH_dlu@test.com' insert into @YourTable select 3,1,'BPT- Sales_AL Conklin_Sales Manager_janetc@test.com' insert into @YourTable select 4,1,'HMD- Sales_Jared Lew_Sales Manager_jared@test.com' ;with cte_tally as ( select top 10000 ROW_NUMBER() over(order by sc1.object_id) N from sys.columns sc1, sys.columns sc2 ) , cte_split as ( select YT.IID, row_number()over(partition by IID order by N) as rn, substring('_'+YT.Value+'_',N+1,charindex('_','_'+YT.Value+'_',N+1)-N-1) as SplitString from cte_tally join @YourTable YT on N < len('_'+YT.Value+'_') where substring('_'+YT.Value+'_',N,1) = '_' ) select IID, max(case when rn = 1 then SplitString end) as [department], max(case when rn = 2 then SplitString end) as [user], max(case when rn = 3 then SplitString end) as [title], max(case when rn = 4 then SplitString end) as [email] from cte_split group by IID
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

psmittyy14 avatar image
psmittyy14 answered
![alt text][1] > **Data** > SELECT * FROM SSLISTVALUES WHERE LISTID = 1 > **So each row here needs to come out into four columns.** > **Like This:** > ![alt text][2] ![alt text][3] **My Query that only returns one value instead of 30** ; WITH cte_tally AS ( SELECT TOP 10000 ROW_NUMBER() OVER ( ORDER BY sc1.object_id ) N , Value FROM sys.columns sc1 , sys.columns sc2 , [ssListValues] sslv ), cte_split AS ( SELECT ROW_NUMBER() OVER ( ORDER BY N ) AS rn , SUBSTRING('_' + Value + '_', N + 1, CHARINDEX('_', '_' + Value + '_', N + 1) - N - 1) AS SplitString FROM cte_tally WHERE N < LEN('_' + Value + '_') AND SUBSTRING('_' + Value + '_', N, 1) = '_' ) SELECT MAX(CASE WHEN rn = 1 THEN SplitString END) AS [department] , MAX(CASE WHEN rn = 2 THEN SplitString END) AS [user] , MAX(CASE WHEN rn = 3 THEN SplitString END) AS [title] , MAX(CASE WHEN rn = 4 THEN SplitString END) AS [email] FROM cte_split [1]: http://puu.sh/9I09Y/31d2684904.png [2]: http://puu.sh/9I0dq/2e2dc87052.png [3]: http://puu.sh/9I00X/f5f9d10e9e.png
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
I've added an example to my answer...
0 Likes 0 ·
psmittyy14 avatar image
psmittyy14 answered
YOU ARE THE BEST! THANK YOU SO MUCH!!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.