SQLTeam.com | Weblogs | Forums

Find Customer Code from Comment Field


#1

I have a comment field which contains customer code for example:
Comment: Customer
Reg: 1 Inv:I00075 Cus:IS00002 Br:ID IS00002
Customer Code is IS00002
Customer always starts with IS and has 7 char. Customer Code is not always at the same position.

I need to extract the customer code from comment field and display separately in a new column.
Some of the data will not have the Customer Code under the comment field, in this case the Customer Column needs to be blank.

Thanks


#2
SELECT LEFT(STUFF(CommentCol,1,CHARINDEX('Cus:IS',CommentCol+'Cus:IS')+3,''),7)
FROM YourTable;

#3

Thanks, this helps however sometimes my Customer Code starts with ID as oppose IS. It could vary but if will have the same number of characters.