SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Substring and Charindex


Substring and Charindex

Author
Message
The_V
The_V
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 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?
Possinator
Possinator
SSC Eights!
SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)

Group: General Forum Members
Points: 968 Visits: 1123
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..
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214026 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
The_V
The_V
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 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.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25903 Visits: 12494
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search