Parsing description - 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 description

Author

Topic

phong919
Starting Member

29 Posts

Posted - 2008-06-24 : 21:53:58

Hello all,

I'm looking at somebody's sql and not sure how to tweak it.

the following column in a table contains description as follow

M3 - abc
M3.1 - ddddddddd
M5.33 - ccccccccccccccc

How can i get the following results in another column

M3
M3.1
M5.33

The following doesn't seem like it's working

CASE WHEN isnumeric(substring(Task_Name , 2 , 1)) = 1 AND isnumeric(substring(Task_name , 3 , 1)) = 1 THEN substring(Task_name , 1 , 4) WHEN isnumeric(substring(Task_name , 2 , 1)) = 1 THEN substring(Task_name , 1 , 2) END

Please assist if possible. thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-24 : 22:50:03

[code]DECLARE @TABLE TABLE
(
col varchar(30)
)
INSERT INTO @TABLE (col)
SELECT 'M3 - abc' UNION ALL
SELECT 'M3.1 - ddddddddd' UNION ALL
SELECT 'M5.33 - ccccccccccccccc'

SELECT col,
ext = CASE WHEN CHARINDEX('-', col) > 0
THEN left(col, CHARINDEX('-', col) - 1)
ELSE col
END
FROM @TABLE

/*
col ext
------------------------------ ------------------------------
M3 - abc M3
M3.1 - ddddddddd M3.1
M5.33 - ccccccccccccccc M5.33
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

phong919
Starting Member

29 Posts

Posted - 2008-06-24 : 23:19:55

Thank you for your help. I used your case when in one of the column and it worked out good.

phong919
Starting Member

29 Posts

Posted - 2008-06-24 : 23:46:53

how would i get just the description after M3 - ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 00:47:48

quote:
Originally posted by phong919

how would i get just the description after M3 - ?

SELECT	col, 
	description = CASE 	WHEN CHARINDEX('-', col) > 0
		THEN substring(col, CHARINDEX('-', col) + 1,LEN(col)) 
		ELSE NULL
		END
FROM	@TABLE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources