substring question 1

[fthomas1973]

I have a substring that I am trying to work out

Here is the source data pattern.
8HTD9R||
or
8HTD9R|||
or
1S494PX|1O00J2U||

I have taken the value before the first | and pulled it into a column.

(SELECT LEFT(SU_Value3, CHARINDEX('|',(SU_Value3))-1)) AS IDX

I need to pull the value between two pipes into another column. In the example above I can’t figure out how to pull 1O00J2U into a column and leave it null if there is nothing there. This is close but not exactly what I want. It doesn't ignore the | that trail

(SELECT RIGHT(RTRIM(SU_Value3), PATINDEX('%|%',(SU_Value3))+1)) AS WORKFLOW

Thanks

 

[r937]

how sure are you that there will always be at least one pipe?

for instance, if there is none, your code fails --

LEFT(SU_Value3, CHARINDEX('|',(SU_Value3))-1))

because CHARINDEX would return 0 and you can't take a negative LEFT ;-)

if there will always be 2 pipes for sure, the expression to pull out the second pipe-delimited word is easy



r937.com | rudy.ca

 

[r937]

SELECT SU_Value3
     , NULLIF( LEFT(IDY,CHARINDEX('|',IDY)-1),'') AS Workflow
 FROM ( 
SELECT SU_Value3
     , RIGHT(SU_Value3,LEN(SU_Value3)-CHARINDEX('|',SU_Value3)) AS IDY 
 FROM SOMESTRINGS2
) AS D

r937.com | rudy.ca

 

[fthomas1973]

There will always be at least one pipe.

 

[djj55]

How about something like

substring(idy,charindex('|',idy)+1, charindex('|',idy, 2)-charindex('|',idy, 1))

Not tested but maybe it will spark an idea
djj

 

[fthomas1973]

I am not real skilled with subqueries so I am having a hard time with the syntax

Here is the complete query

SELECT   
SU_Name AS CABINET,  
SU_Key_ID2 AS DOCTYPE, SU_Value1 AS DOC_DESCRIPTION, 
SU_Value2 AS SUBFOLDER,
(SELECT LEFT(SU_Value3, CHARINDEX('|',(SU_Value3))-1)) AS IDX, 
(SELECT RIGHT(RTRIM(SU_Value3), PATINDEX('%|%',(SU_Value3))+1)) AS WORKFLOW
FROM         Support_Tab
WHERE     (SU_Key_ID1 = 'doctitle')
ORDER BY SU_Name, SU_Key_ID2

How does your code fit into this?

Thanks

 

[r937]

SELECT CABINET
     , DOCTYPE
     , DOC_DESCRIPTION
     , SUBFOLDER
     , NULLIF( LEFT(IDY,CHARINDEX('|',IDY)-1),'') AS Workflow
  FROM (
SELECT SU_Name AS CABINET
     , SU_Key_ID2 AS DOCTYPE
     , SU_Value1 AS DOC_DESCRIPTION
     , SU_Value2 AS SUBFOLDER
     , RIGHT(SU_Value3,LEN(SU_Value3)-CHARINDEX('|',SU_Value3)) AS IDY
  FROM Support_Tab
 WHERE SU_Key_ID1 = 'doctitle'
       ) AS D
ORDER 
    BY CABINET
     , DOCTYPE

r937.com | rudy.ca