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.
Comment below in answers. Allowed me to post pictures of the data as well.
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])
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
![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
YOU ARE THE BEST! THANK YOU SO MUCH!!
15 People are following this question.
