Count Number of Number (Numeric) Characters

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

Count Number of Number (Numeric) Characters

Author

Topic

rickincanada
Starting Member

18 Posts

Posted - 2011-11-07 : 10:19:00

Hello,

I am attempting to implement a WHERE clause on a pretty simple SELECT that will only show me the records that contain 10 or more number characters. Effectively I have a VARCHAR(8000) column from which I want to only see rows where there are 10 or more numeric values in this column. An example would be "Toll Free 1-888-555-1212" would appear in the SELECT as it contains 11 numeric characters. Where as "Call before 9AM but no earlier than 6AM" would not as it only contains 2 numeric characters.

Is this possible? If so, can you give me some direction please?

Thanks in advance!

Rick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 10:23:28

see logic here

http://beyondrelational.com/blogs/madhivanan/archive/2011/02/28/extract-only-numbers-from-a-string-part-3.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-07 : 10:24:21

Probably an easier way but

where len(fld) - len(replace(.......replace(replace(fld,'0','','1',''),......,'9','')) >= 10

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rickincanada
Starting Member

18 Posts

Posted - 2011-11-07 : 10:26:23

Is it not attainable without creating a function?

Thanks,
Rick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 10:27:03

quote:
Originally posted by rickincanada

Is it not attainable without creating a function?

Thanks,
Rick

see logic Nigel gave if you dont want to use a function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rickincanada
Starting Member

18 Posts

Posted - 2011-11-07 : 10:34:25

Thanks Nigel - except I maybe be a little green to translate what you've given me...

I assume that I need to add some additional information to your clause:

where len(fld) - len(replace(.......replace(replace(fld,'0','','1',''),......,'9','')) >= 10

As it tells me that replace requires 3 arguments.

I tried modifying it to this below, however I still get the same error..

len([notep_notes]) - len(replace(replace(replace([notep_notes],'0','1','2','3','4','5','6','7','8','9','')) >= 10

Thanks for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 10:38:33

it should be


...
len(fld) - len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(fld,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','')) >=10
....

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rickincanada
Starting Member

18 Posts

Posted - 2011-11-07 : 10:40:32

Thanks SO much - worked perfect!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources