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

1BACGENTPN_CF_10202011

2BTPN_09232010 | Vendor:

3BTPN_09232010 | Vendor:

4 BACGENTPN_07152010 | Vendor:

5 BACGENTPN_07152010 | Vendor:

6BACLITTPN_11262010,LMO 0416 Cover Letter|Vendor: S

7BACGENTPN_08312010 | Ven

8BACGENTPN_08312010 | Ven

9BACGENTPN_08312010 | Ven

10BACGENTPN_08312010 | Ven

11BACGENTPN_08312010 | Ven

12BACGENTPN_08312010 | Ven

13BACGENTPN_08312010 | Ven

14 BACBKPTPN_08242010 | Vendor:

15 BACBKPTPN_08242010 | Vendor:

16 BACBKPTPN_09092010 | Vendor:

17 BACBKPTPN_09092010 | Vendor:

18BACGENTPN_09212010 | Vendor:

19BACGENTPN_09212010 | Vendor:

20BACGENTPN_08312010 | Ven

21BACGENTPN_08312010 | Ven

22BTPN_09232010 | Vendor:

23BACBKPTPN_01272011_V2| Vendor:S

24BACBKPTPN_01272011_V2| Vendor:S

25BTPN_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?