separating integer value - 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

separating integer value

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-02-16 : 00:18:07

Dear All,

I have a column of varchar(50) with values like
01 - Record Not exist
02 - Unmatched Doctor
03 - Error added
--

--

10 - Record not added

And its fixed value no more values will come in future.

I want to separate only integer value to take further filter condition how do we take out only 01,02,03...10 kind of values from the table having records morethan 1000.

Thanks,
Gangadhar

Thanks,
Gangadhara MS
SQL Developer and DBA

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-16 : 00:43:35

Select SubString(ColumnName,PATINDEX('[0-9]%',ColumnName),PATINDEX('%[0-9][^0-9]%',ColumnName)) from TableName

Cheers
MIK

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-16 : 02:35:53

convert(int,left(ColumnName,2))

No, you're never too old to Yak'n'Roll if you're too young to die.

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-02-16 : 03:37:10

declare @table table
(
col1 varchar(50)
)
insert @table
select '01 - Record Not exist' union all
select '02 - Unmatched Doctor' union all
select '03 - Error added' union all
select '1000- error'

Select SubString(Col1,PATINDEX('[0-9]%',col1),PATINDEX('%[0-9][^0-9]%',col1)) intvalue,col1 from @table

--Ranjit

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-16 : 09:24:02

quote:
Originally posted by Ranjit.ileni

declare @table table
(
col1 varchar(50)
)
insert @table
select '01 - Record Not exist' union all
select '02 - Unmatched Doctor' union all
select '03 - Error added' union all
select '1000- error'

Select SubString(Col1,PATINDEX('[0-9]%',col1),PATINDEX('%[0-9][^0-9]%',col1)) intvalue,col1 from @table

--Ranjit

or

Select left(Col1,PATINDEX('%[^0-9]%',col1)-1) ,col1 from @table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources