query - 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

query

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2012-03-13 : 12:05:24

Hi All,

I have this in one of my table
TABLE1


Col1                         col2     Col3
100  TECHNICAL 12
110. TECHNICAL: 12
123  TECHNICAL- 12

I want to do like this


Col1                         col2     Col3
100  TECHNICAL 12            100      12
110. TECHNICAL: 13           110      13
123  TECHNICAL- 12           123      12

is it possible to do that?

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-13 : 12:18:22

Yes - for example:

SELECT 
	Col1, 
	STUFF(LTRIM(Col1),PATINDEX('%[^0-9]%',LTRIM(Col1)),LEN(Col1),'') AS Col2,
	REVERSE(STUFF(REVERSE(RTRIM(Col1)),PATINDEX('%[^0-9]%',REVERSE(RTRIM(Col1))),LEN(Col1),'')) AS Col3
FROM Table1

anjali5
Posting Yak Master

121 Posts

Posted - 2012-03-13 : 14:08:38

Thank you!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources