Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Substring and Charindex Expand / Collapse
Author
Message
Posted Monday, October 24, 2011 4:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 21, 2011 7:44 PM
Points: 17, Visits: 41
I am using the following case statement to extract information from a long string with the '|' symbol listed multiple times in the string.

Case
when Len(F.Comments) is null
then Null

when Left(F.Comments,Charindex('|', F.Comments)-1) like 'File Name:%'
then Substring(F.Comments,11,CHARINDEX( '|', F.Comments)-1)

when Left(F.Comments,Charindex('|', F.Comments)-1) like 'File:%'
then Substring(F.Comments,6,CHARINDEX( '|', F.Comments)-1)

when (F.Comments is not Null or Left(F.Comments,Charindex('|', F.Comments,1)-1) not like 'File Name:%'
or Left(F.Comments,Charindex('|', F.Comments)-1) not like 'File:')
then Substring(F.Comments,1,CHARINDEX( '|', F.Comments)-1)
End as Letter_name,


An example of the results are listed below:

ACCT# Letter_name
1 BACGENTPN_CF_10202011
2 BTPN_09232010 | Vendor:
3 BTPN_09232010 | Vendor:
4 BACGENTPN_07152010 | Vendor:
5 BACGENTPN_07152010 | Vendor:
6 BACLITTPN_11262010,LMO 0416 Cover Letter|Vendor: S
7 BACGENTPN_08312010 | Ven
8 BACGENTPN_08312010 | Ven
9 BACGENTPN_08312010 | Ven
10 BACGENTPN_08312010 | Ven
11 BACGENTPN_08312010 | Ven
12 BACGENTPN_08312010 | Ven
13 BACGENTPN_08312010 | Ven
14 BACBKPTPN_08242010 | Vendor:
15 BACBKPTPN_08242010 | Vendor:
16 BACBKPTPN_09092010 | Vendor:
17 BACBKPTPN_09092010 | Vendor:
18 BACGENTPN_09212010 | Vendor:
19 BACGENTPN_09212010 | Vendor:
20 BACGENTPN_08312010 | Ven
21 BACGENTPN_08312010 | Ven
22 BTPN_09232010 | Vendor:
23 BACBKPTPN_01272011_V2| Vendor:S
24 BACBKPTPN_01272011_V2| Vendor:S
25 BTPN_09232010 | Vendor:


Row 1 is the ideal Letter_name I want returned. I only want to return the characters before the 1st '|'. I used substring because the words File: and File Name: were at the beginning of some of the strings. Any suggestions?
Post #1195489
Posted Monday, October 24, 2011 5:42 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 4:36 PM
Points: 495, Visits: 1,122
Try something like this - I wasn't able to test without setting up data, but this should be close:

SELECT
Field=F.Comments
--find starting location: first colon + 1
,StartAt=CHARINDEX( ':',F.Comments) + 1
--find ending location: first pipe - 1
,EndAt=CHARINDEX( '|',F.Comments) - 1
--find number of chars to return (end location - start location)
,NumberOfChars=(CHARINDEX( '|',F.Comments) - 1) - (CHARINDEX( ':',F.Comments) + 1)
--get substring: substring(field, StartAt, NumberOfChars)
,GetMidString=SUBSTRING(F.Comments,CHARINDEX( ':',F.Comments) + 1, (CHARINDEX( '|',F.Comments) - 1) - (CHARINDEX( ':',F.Comments)))
,GetString=SUBSTRING(F.Comments, 0, CHARINDEX( '|',F.Comments) - 1
--put them together
,CASE
WHEN F.Comments IS NULL THEN NULL
WHEN F.Comments LIKE 'File%' THEN SUBSTRING(F.Comments,CHARINDEX( ':',F.Comments) + 1, (CHARINDEX( '|',F.Comments) - 1) - (CHARINDEX( ':',F.Comments)))
ELSE SUBSTRING(F.Comments, 0, CHARINDEX( '|',F.Comments) - 1
END


Looking for a Deadlock Victim Support Group..
Post #1195512
Posted Monday, October 24, 2011 6:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750
The_V (10/24/2011)
I am using the following case statement to extract information from a long string with the '|' symbol listed multiple times in the string.

Case
when Len(F.Comments) is null
then Null

when Left(F.Comments,Charindex('|', F.Comments)-1) like 'File Name:%'
then Substring(F.Comments,11,CHARINDEX( '|', F.Comments)-1)

when Left(F.Comments,Charindex('|', F.Comments)-1) like 'File:%'
then Substring(F.Comments,6,CHARINDEX( '|', F.Comments)-1)

when (F.Comments is not Null or Left(F.Comments,Charindex('|', F.Comments,1)-1) not like 'File Name:%'
or Left(F.Comments,Charindex('|', F.Comments)-1) not like 'File:')
then Substring(F.Comments,1,CHARINDEX( '|', F.Comments)-1)
End as Letter_name,


An example of the results are listed below:

ACCT# Letter_name
1 BACGENTPN_CF_10202011
2 BTPN_09232010 | Vendor:
3 BTPN_09232010 | Vendor:
4 BACGENTPN_07152010 | Vendor:
5 BACGENTPN_07152010 | Vendor:
6 BACLITTPN_11262010,LMO 0416 Cover Letter|Vendor: S
7 BACGENTPN_08312010 | Ven
8 BACGENTPN_08312010 | Ven
9 BACGENTPN_08312010 | Ven
10 BACGENTPN_08312010 | Ven
11 BACGENTPN_08312010 | Ven
12 BACGENTPN_08312010 | Ven
13 BACGENTPN_08312010 | Ven
14 BACBKPTPN_08242010 | Vendor:
15 BACBKPTPN_08242010 | Vendor:
16 BACBKPTPN_09092010 | Vendor:
17 BACBKPTPN_09092010 | Vendor:
18 BACGENTPN_09212010 | Vendor:
19 BACGENTPN_09212010 | Vendor:
20 BACGENTPN_08312010 | Ven
21 BACGENTPN_08312010 | Ven
22 BTPN_09232010 | Vendor:
23 BACBKPTPN_01272011_V2| Vendor:S
24 BACBKPTPN_01272011_V2| Vendor:S
25 BTPN_09232010 | Vendor:


Row 1 is the ideal Letter_name I want returned. I only want to return the characters before the 1st '|'. I used substring because the words File: and File Name: were at the beginning of some of the strings. Any suggestions?


Could you post the unmodified data for the first 6 lines of results you have above? Please... if there's any private information in the data, just say "No. There's private information in the data" or if you can obfuscate the private info so that you could post the data, that would be handy. Thanks.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1195519
Posted Tuesday, October 25, 2011 4:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 21, 2011 7:44 PM
Points: 17, Visits: 41
No, I cannot post the unmodified data, it is private. The code and illustration details what is returned. I changed the account numbers which is not significant.
Post #1195599
Posted Tuesday, October 25, 2011 5:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:16 PM
Points: 7,738, Visits: 9,487
The_V (10/24/2011)
I am using the following case statement to extract information from a long string with the '|' symbol listed multiple times in the string.

Case
when Len(F.Comments) is null
then Null

when Left(F.Comments,Charindex('|', F.Comments)-1) like 'File Name:%'
then Substring(F.Comments,11,CHARINDEX( '|', F.Comments)-1)

when Left(F.Comments,Charindex('|', F.Comments)-1) like 'File:%'
then Substring(F.Comments,6,CHARINDEX( '|', F.Comments)-1)

when (F.Comments is not Null or Left(F.Comments,Charindex('|', F.Comments,1)-1) not like 'File Name:%'
or Left(F.Comments,Charindex('|', F.Comments)-1) not like 'File:')
then Substring(F.Comments,1,CHARINDEX( '|', F.Comments)-1)
End as Letter_name,


An example of the results are listed below:

ACCT# Letter_name
1 BACGENTPN_CF_10202011
2 BTPN_09232010 | Vendor:
3 BTPN_09232010 | Vendor:
4 BACGENTPN_07152010 | Vendor:
5 BACGENTPN_07152010 | Vendor:
6 BACLITTPN_11262010,LMO 0416 Cover Letter|Vendor: S
7 BACGENTPN_08312010 | Ven
8 BACGENTPN_08312010 | Ven
9 BACGENTPN_08312010 | Ven
10 BACGENTPN_08312010 | Ven
11 BACGENTPN_08312010 | Ven
12 BACGENTPN_08312010 | Ven
13 BACGENTPN_08312010 | Ven
14 BACBKPTPN_08242010 | Vendor:
15 BACBKPTPN_08242010 | Vendor:
16 BACBKPTPN_09092010 | Vendor:
17 BACBKPTPN_09092010 | Vendor:
18 BACGENTPN_09212010 | Vendor:
19 BACGENTPN_09212010 | Vendor:
20 BACGENTPN_08312010 | Ven
21 BACGENTPN_08312010 | Ven
22 BTPN_09232010 | Vendor:
23 BACBKPTPN_01272011_V2| Vendor:S
24 BACBKPTPN_01272011_V2| Vendor:S
25 BTPN_09232010 | Vendor:


Row 1 is the ideal Letter_name I want returned. I only want to return the characters before the 1st '|'. I used substring because the words File: and File Name: were at the beginning of some of the strings. Any suggestions?


Try changing
Substring(F.Comments,11,CHARINDEX( '|', F.Comments)-1)
to
Substring(F.Comments,11,CHARINDEX( '|', F.Comments)-11) -- or maybe -12?
and also change
Substring(F.Comments,6,CHARINDEX( '|', F.Comments)-1)
to
Substring(F.Comments,6,CHARINDEX( '|', F.Comments)-7) -- or maybe -6?

The reason is that the third parameter of substring is the number of characters included, not the last wanted position in the original string.


Tom
Post #1195624
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse