Looking for a number in a string

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 2005 Forums

Transact-SQL (2005)

Looking for a number in a string

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-01-17 : 01:07:20

Hi I have a postcode field that is varchar. I know that some of the postcodes have been entered incorrectly (they do not have a number in them)
I want to run a query that will show me the records that do not have a number anywhere in the field.
I can't think how to go about this, any help would be really appreciated.
ICW

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-01-17 : 01:35:25

here's one way

select * from table1 a
where not (a.field like '%1%') and
not (a.field like '%2%') and
not (a.field like '%3%') and
not (a.field like '%4%') and
not (a.field like '%5%') and
not (a.field like '%6%') and
not (a.field like '%7%') and
not (a.field like '%8%') and
not (a.field like '%9%') and
not (a.field like '%0%')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-17 : 02:38:58

This can be considered too

SELECT * FROM <YourTableNameHere> WHERE <YourColumnNameHere> NOT LIKE '%[0-9]%'

Peter Larsson
Helsingborg, Sweden

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-01-17 : 03:11:33

thanks a lot guys

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-17 : 03:17:30

Are you talking about english post codes?
If so, is there a way to check complete structure of post code?

Post code 4NG E34:
First digit is always numeric. Second and third digit are always alpha.
Fourth digit is alpha. Fifth and sixth are always numeric.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources