CODEWITHSUNDEEP
sqlsql-server-2012
Mike R
Mike R

Reputation: 3

Substring, Charindex

I'm trying to extract a code that varies in length that exists after the first two underscores and before the third underscore in a field in a table. The table looks something like this. There are greater than 30 codes and I need to extract the code as part of a query.

code
====
XX_YYY_CODE1_ZZZ
XX_YYY_CODE2_ZZZ
XX_YYY_CODE3_ZZZ
XX_YYY_CODE4_ZZZ
...

I've tried using this code however this gives me YYY rather than the CODE I'm after and I can't work out how to re-engineer it to do what I want it to do.

select 
left(SUBSTRING(code, 
CHARINDEX('_', code) + 1, len(code)), 
CHARINDEX('_',SUBSTRING(code,CHARINDEX('_', code) + 2, LEN(code))))

Upvotes: 0

Views: 2647

Answers (6)

Mike R
Mike R

Reputation: 3

A slightly modified answer from dotNET does what I needed. Examples below;

SELECT SUBSTRING
('XX_YYY_LongCode_ZZZ', 
  CHARINDEX('_', 'XX_YYY_LongCode_ZZZ', CHARINDEX
  ('_', 'XX_YYY_LongCode_ZZZ') + 1) + 1, 
  LEN('XX_YYY_LongCode_ZZZ') - 
  (CHARINDEX('_', 'XX_YYY_LongCode_ZZZ', CHARINDEX   
  ('_', 'XX_YYY_LongCode_ZZZ') + 1)) -
  CHARINDEX('_',REVERSE('XX_YYY_LongCode_ZZZ'))
)

Or another example

SELECT SUBSTRING
('XX_YYY_otherCode_ZZZ', 
  CHARINDEX('_', 'XX_YYY_otherCode_ZZZ', CHARINDEX    
  ('_', 'XX_YYY_otherCode_ZZZ') + 1) + 1, 
  LEN('XX_YYY_otherCode_ZZZ') - 
  (CHARINDEX('_', 'XX_YYY_otherCode_ZZZ', CHARINDEX 
  ('_', 'XX_YYY_otherCode_ZZZ') + 1)) -
  CHARINDEX('_',REVERSE('XX_YYY_otherCode_ZZZ'))
)

Upvotes: 0

Arijit
Arijit

Reputation: 1674

Just modified your code. It should work.

select 
SUBSTRING(
substring(substring(code,CHARINDEX('_', code)+1,len(code))
,CHARINDEX('_', substring(code
,CHARINDEX('_', code)+1,len(code)))+1
,len(code)),1,
LEN(substring(substring(code,CHARINDEX('_', code)+1,len(code))
,CHARINDEX('_', substring(code
,CHARINDEX('_', code)+1,len(code)))+1
,len(code)))
- len(substring(substring(substring(code,CHARINDEX('_', code)+1,len(code))
,CHARINDEX('_', substring(code
,CHARINDEX('_', code)+1,len(code)))+1
,len(code)),CHARINDEX('_', substring(substring(code,CHARINDEX('_', code)+1,len(code))
,CHARINDEX('_', substring(code
,CHARINDEX('_', code)+1,len(code)))+1
,len(code)))+1,
LEN(substring(substring(code,CHARINDEX('_', code)+1,len(code))
,CHARINDEX('_', substring(code
,CHARINDEX('_', code)+1,len(code)))+1
,len(code)))))-1)

Upvotes: 0

Kapil
Kapil

Reputation: 987

I am reversing the string and doing the substring operation.

 CREATE TABLE #MyTable(Code VARCHAR(50))

 INSERT INTO #MyTable( Code)
 SELECT 'XX_YYY_Code1_ZZZ' UNION ALL
 SELECT 'XX_YYY_Code2_ZZZ' UNION ALL
 SELECT 'XX_YYY_Code3_ZZZ' UNION ALL
 SELECT 'XX_YYY_Code4_ZZZ' 

select Reverse(Substring
(substring (Reverse(code),charindex('_',Reverse(code))+1,Len(code)),0,
CHARINDEX('_',substring (Reverse(code),charindex('_',Reverse(code))+1,Len(code)))))
from #Table

Upvotes: 0

Anaioer
Anaioer

Reputation: 41

if only four underscore,Use below code

select parsename(replace('1_2_3_4','_','.'),2)

Upvotes: 4

dotNET
dotNET

Reputation: 35380

Try this expression instead:

SELECT SUBSTRING
(code, 
  CHARINDEX('_', code, CHARINDEX('_', code) + 1) + 1, 
  LEN(code) - 
  (CHARINDEX('_', code, CHARINDEX('_', code) + 1) + 1) -
  CHARINDEX('_',REVERSE(code))
)

Upvotes: 1

Mansoor
Mansoor

Reputation: 4192

Use below code,I hope it is give your expected result :

 CREATE TABLE #Table(GvnString VARCHAR(100))

 INSERT INTO #Table( GvnString )
 SELECT 'XX_YYY_Code1_ZZZ' UNION ALL
 SELECT 'XX_YYY_Code2_ZZZ' UNION ALL
 SELECT 'XX_YYY_Code3_ZZZ' UNION ALL
 SELECT 'XX_YYY_Code4_ZZZ' 

 SELECT SUBSTRING(GvnString, CHARINDEX('_', GvnString, CHARINDEX('_', 
        GvnString) + 1) + 1, LEN(GvnString) - CHARINDEX('_', GvnString, 
        CHARINDEX('_', GvnString) + 1))
 FROM #Table

Upvotes: 0

Related Questions