Removing numerics that start with a 2

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

SQL Server 2008 Forums

Transact-SQL (2008)

Removing numerics that start with a 2

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-06-27 : 14:19:09

I would like to be able to exclude numeric characters that start with a 2. Sample data is below:

COLA (varchar(5)
A33
100
200
210
C13
222
300
400
D4F

My result set should be:

COLA
A11
100
C13
300
400
D4F

Thank you!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-27 : 14:20:03

WHRE COLA NOT LIKE '2%'

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-27 : 14:20:14

[code]SELECT COLA WHERE COLA NOT LIKE '2%'[/code]

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-06-27 : 14:25:12

Should have said that I want to remove values that are in the 200-300 range....

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-06-27 : 14:32:47

Getting closer using:

COLA NOT BETWEEN 200 AND 300.

My issue now is the conversion error I am getting.

"Conversion failed when converting the nvarchar value 'A11' to data type int."

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-27 : 14:57:00

quote:
Originally posted by qman

Getting closer using:

COLA NOT BETWEEN 200 AND 300.

My issue now is the conversion error I am getting.

"Conversion failed when converting the nvarchar value 'A11' to data type int."

Doesn't the query Lamprey and I posted don't work? If you want to use what you posted, do it like this:

COLA NOT BETWEEN '200' AND '300'

That will reject 300. If you want to keep 300 use :

NOT (COLA >= '200' AND COLA <'300')

But really, given your specification, what we posted earlier is a better approach.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources