question

0107_2008R2 avatar image
0107_2008R2 asked

Issue with Charindex

I am attempting to pull information from a string with multiple parts with periods. I managed to pull the first two parts of information but struggling with the third [ele3] and fourth [ele4] elements. The fourth element is only relevant for some data, and I have included this below. /* The string may contain 2 or 3 periods "full stops", the goal is to get what is in between the "." --2 periods '6201.D310.Z914' --3 periods '5405.L40.S300.XL' */ declare @a varchar(50) select @a = '5405.L40.S300.XL' SELECT @a as nominal_account, LEFT(@a, CHARINDEX('.',@a +'.')-1) as [account] ,SUBSTRING(@a, -- Start from first . CHARINDEX('.',@a+'.')+1, -- Position of the second "." minus the position of the first "." to find length. CHARINDEX('.',@a+'.',CHARINDEX('.',@a+'.')+1)-1 - CHARINDEX('.',@a+'.')) as [ele2] ,SUBSTRING(@a, -- Start from second "." CHARINDEX('.',@a+'.',CHARINDEX('.',@a+'.')), -- Position of the third "." minus the position of the second "." to find length. CHARINDEX('.',@a+'.',CHARINDEX('.',@a+'.',CHARINDEX('.',@a+'.'))) - CHARINDEX('.',@a+'.',CHARINDEX('.',@a+'.'))) as [ele3] ,SUBSTRING(@a, -- Start from third "." CHARINDEX('.',@a+'.',CHARINDEX('.',@a+'.',CHARINDEX('.',@a+'.'))), -- Position of the fourth "." minus the position of the third "." to find length. CHARINDEX('.',@a+'.',CHARINDEX('.',@a+'.',CHARINDEX('.',@a+'.',CHARINDEX('.',@a+'.')))) - CHARINDEX('.',@a+'.',CHARINDEX('.',@a+'.',CHARINDEX('.',@a+'.')))) as [ele4]
substringcharindex
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.

Many thanks to the two responses so far (Dave and Scot) It might be better that the data on the two part period entry is added to the field as three i.e. 6201.D310.Z914. and so the fourth part should be returned blank. I need to pull the elements into the same row as separate fields, and therefore need a suitable solution.
0 Likes 0 ·
Scot Hauder avatar image
Scot Hauder answered
DECLARE @a varchar(50) SELECT @a = '5405.L40.5300.XL' SELECT @a [nominal_account] ,REVERSE(PARSENAME(REVERSE(@a),1))[account] ,REVERSE(PARSENAME(REVERSE(@a),2))[ele1] ,REVERSE(PARSENAME(REVERSE(@a),3))[ele2] ,ISNULL(REVERSE(PARSENAME(REVERSE(@a),4)),'')[ele3] If you have more than four parts you will need something more complex
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.

Apologies Scot, that works for me many thanks ;-)
0 Likes 0 ·
Dave_Green avatar image
Dave_Green answered
Your solution is, as I think you have found, quite complex to scale. I'm going to assume that you may (in future) have 5 or more periods. I would use a CTE to split this out. So I would use a CTE like this: ;WITH CTE (main -- This is the part of the string which has been isolated. ,part, -- This is the order which the parts appeared in the string rest --This will store the "remainder" - i.e. the bit of the string @a left over ) AS ( SELECT LEFT(@a,CHARINDEX('.',@a,0)-1) /* Everything before we find a period */,1 /* Part number */,RIGHT(@a,LEN(@a)-CHARINDEX('.',@a,1)) /* What's left */ --This line picks out the first combination, and the remainder UNION ALL --The next bit will take the output, and cause it to run the same logic on each "remainder" until we run out of remainders SELECT CASE WHEN CHARINDEX('.',rest) = 0 THEN rest ELSE LEFT(rest,CHARINDEX('.',rest)-1) end,part+1, CASE WHEN CHARINDEX('.',rest)=0 THEN '' ELSE RIGHT(rest,LEN(rest)-CHARINDEX('.',rest,1)) end FROM CTE WHERE rest != '' ) This produces a table CTE which can be either queried to display our parts as rows: SELECT main,part FROM CTE --<<-- This gives you a simple breakdown in row format - pick the part you want or alternatively you can use the PIVOT operator to give you something very similar to what you had before: SELECT @a AS nominal_account, MAX([1]) AS account, MAX([2]) AS ele2, MAX([3]) AS ele3, MAX([4]) AS ele4 FROM CTE PIVOT ( max(main) FOR part IN ([0], [1], [2], [3], [4]) ) AS PivotTable; You want to put the CTE section with whichever output style suits your need best. The advantage of this approach is that it scales, but also that it can be easily extended to draw from a table if required. Whilst there is a CTE limit (default 100 recursions, so 100 "parts" to your string), your question implies that you will not reach this any time soon. I hope that helps.
10 |1200

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

0107_2008R2 avatar image
0107_2008R2 answered
Using the data for p.nominal_account 6201.D310.Z914 5405.L40.S300.XL SELECT p.nominal_account [nominal_account] ,REVERSE(PARSENAME(REVERSE(p.nominal_account),1))[account] ,REVERSE(PARSENAME(REVERSE(p.nominal_account),2))[ele1] ,REVERSE(PARSENAME(REVERSE(p.nominal_account),3))[ele2] ,case when REVERSE(PARSENAME(REVERSE(p.nominal_account),4)) is null then '' else REVERSE(PARSENAME(REVERSE(p.nominal_account),4)) end [ele3] Adding the case clause the results are as follows: nominal_account account ele1 ele2 ele3 6201.D310.Z914 6201 D310 Z914 5405.L40.S300.XL 5405 L40 S300 XL
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.