String Manipulation

I'm trying to manipulate a string column with string data like this:

sometimes the first number before the ';' is a single digit, but sometimes it can be above 10

1;49yihjkflo5968gjvn,product1,01/01/2011 -- Single Digit before the ';'
10;4568ujghty574ifk42,product4,01/09/2011 -- Double Digit before the ';'

what i'm trying to do is first select the account number, alwas 18 characters, which stars just after the first ';' then, for the second column the everthing before the first ';' coulbe be 1 or 2 digits.

so outcome of my query should look something like this:

ACCOUNT_ID                     PRODUCT_INFO
49yihjkflo5968gjvn             1;product1,01/01/2011
4568ujghty574ifk42            10;product4,01/09/2011

i've tried it like this, but it chops off either the 2nd digit of the Number of product_info, or it chops off the last digit of the account_id if the first digit is two characters.

select substring(MM_Prod_Info,3,18)as AccountID, substring(MM_Prod_Info, charindex(';',MM_Prod_Info)-1,len(MM_Prod_Info)) as ProductInfo from mm_prod_codes

select substring(MM_Prod_Info,3,18)as AccountID, substring(MM_Prod_Info, charindex(';',MM_Prod_Info)-1,len(MM_Prod_Info)) as ProductInfo from mm_prod_codes

Open in new window

LVL 1
Auerelio VasquezETL DeveloperAsked:
Who is Participating?
 
JoeNuvoConnect With a Mentor Commented:
a little mistake, here is the fix

SELECT
	SUBSTRING(MM_Prod_Info, CHARINDEX(';', MM_Prod_Info) +1 , 18) AS ACCOUNT_ID,
	REPLACE(MM_Prod_Info, SUBSTRING(MM_Prod_Info, CHARINDEX(';', MM_Prod_Info) +1 , 19), '') AS PRODUCT_INFO
FROM
	mm_prod_codes

Open in new window

0
 
JoeNuvoCommented:
SELECT
	SUBSTRING(MM_Prod_Info, CHARINDEX(';', MM_Prod_Info) +1 , 18) AS ACCOUNT_ID,
	REPLACE(MM_Prod_Info, SUBSTRING(MM_Prod_Info, CHARINDEX(';', MM_Prod_Info) , 19), '') AS PRODUCT_INFO
FROM
	mm_prod_codes

Open in new window

0
 
mcv22Commented:

SELECT
	SUBSTRING(MM_Prod_Info, CHARINDEX(';', MM_Prod_Info) + 1, 18) AS Account_ID,
	SUBSTRING(MM_Prod_Info, 1, CHARINDEX(';', MM_Prod_Info)) + SUBSTRING(MM_Prod_Info, CHARINDEX(',', MM_Prod_Info) + 1, LEN(MM_Prod_Info)) as ProductInfo 
FROM
	mm_prod_codes

Open in new window

0
 
Auerelio VasquezETL DeveloperAuthor Commented:
Thanks that was great and useful!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.