?
Solved

String Manipulation

Posted on 2011-02-17
4
Medium Priority
?
379 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Auerelio Vasquez
  • 2
4 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 34922798
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
 
LVL 12

Expert Comment

by:mcv22
ID: 34922815

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
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 2000 total points
ID: 34922829
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
 
LVL 1

Author Closing Comment

by:Auerelio Vasquez
ID: 34923059
Thanks that was great and useful!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question