Parsing the characters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Parsing the characters

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2012-03-16 : 12:01:41

Hi All,

I have this table that I have to parse one column to different column


TBLE A
COL1                                          COL2    COL3
A) TECHNICAL:2 B) TECHNICAl ASSIST:9
A. TECHNICAL:3 B. TECHNICAl ASSIST:10
A TECHNICAL 5  B TECHNICAL:20
A TECHNICAL: 5  B TECHNICAL:20

I need the output like below


TBLE A
COL1                                          COL2    COL3
A) TECHNICAL:2 B) TECHNICAl ASSIST:9           2       9
A. TECHNICAL:3 B. TECHNICAl ASSIST:10          3       10
A TECHNICAL 5  B TECHNICAL:20                  5       20
A TECHNICAL: 5  B TECHNICAL:20                 5       20

I am not sure if it is possible. I started writing the query, but couldn't figure out so i was wondering if someone can help me.

Thanks.

Any help will be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 12:14:06

if format is consistent use something like


UPDATE table
SET Col2=SUBSTRING(COL1,PATINDEX('%:%',COL1)+1,CHARINDEX(' ',COL1,PATINDEX('%:%',COL1)+1)-PATINDEX('%:%',COL1)-1),
COL3=RTRIM(SUBSTRING(COL1,CHARINDEX(':',COL1,PATINDEX('%TECHNICAl ASSIST%',COL1)+1)+1,LEN(COL1)))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-16 : 12:18:14

Of COURSE it's possible

Do you have any Rules?

It's Important to identify ALL of the forms that your Col 1 Has

You need to identify all the Distinct variations

So lets say you do this

SELECT SUBSTRING(Col1,1,13) AS DISTINCT_Col1, COUNT(*)
FROM yourTable
GROUP BY SUBSTRING(Col1,1,13)
ORDER BY 2 DESC

Then Maybe

SELECT SUBSTRING(Col1,16,20) AS DISTINCT_Col2, COUNT(*)
FROM yourTable
GROUP BY SUBSTRING(Col1,16,20)
ORDER BY 2 DESC

Post what you get back

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-16 : 12:19:40

quote:
Originally posted by visakh16

if format is consistent use something like

BWHAHAHAHAHAHAHAHA

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-16 : 12:38:18

How about



CREATE TABLE TABLEA (
		COL1 varchar(max)
	,	COL2 int
	,   COL3 int
)
GO

INSERT INTO TABLEA (COL1) 
SELECT 'A) TECHNICAL:2 B) TECHNICAl ASSIST:9' UNION ALL
SELECT 'A. TECHNICAL:3 B. TECHNICAl ASSIST:10' UNION ALL
SELECT 'A TECHNICAL 5  B TECHNICAL:20' UNION ALL
SELECT 'A TECHNICAL: 5  B TECHNICAL:20'
GO

SELECT s, e-s+1
	, SUBSTRING(COL1,s,e-s-1)
	, CONVERT(int,REPLACE(REPLACE(SUBSTRING(COL1,s,e-s-1),' ',''),':',''))
FROM (
SELECT COL1 
, CHARINDEX('TECHNICAL',COL1) + 9 AS s
, CHARINDEX('B',COL1) + 1 AS e
FROM TABLEA
) AS XXX

Then follow the same kind of thing for B

You still need to understand all the permutations

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2012-03-19 : 16:59:39

Thanks Visakh!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 14:56:48

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-20 : 14:58:58

Thank you What?

The format isn't consistent in the small sample that was provided

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources