string function - 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

SQL Server 2000 Forums

Transact-SQL (2000)

string function

Author

Topic

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-09-19 : 12:27:58

Hi,

I've been trying to find a SQL string function that will tell me if there are only certain characters in a given string. I need to know if a string contains only numbers and dashes (1234567890-) (no alpha chracters). I need something like Instr() function but in SQl.
I am guesing it will have somethig to do with Char() and ASCII() functions?

Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-19 : 12:41:05

How About:




DECLARE @x varchar(8000)

SELECT @x = '1234567890-' 

SELECT CASE WHEN ISNUMERIC(REPLACE(@x,'-','')) = 1 THEN 'TRUE' ELSE 'FALSE' END

SELECT @x = 'BRETT'

SELECT CASE WHEN ISNUMERIC(REPLACE(@x,'-','')) = 1 THEN 'TRUE' ELSE 'FALSE' END

Could probably turn it in to a UDF...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-19 : 13:02:00

if patindex('%[^0-9-]%',@x) = 0

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

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-09-19 : 13:47:40

This is exactly what I needed, THANKS

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-19 : 13:55:26

Very Nice...

But where do I find info about how to decipher

'%[^0-9-]%'

and why it works the way it does...??

masks?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-19 : 14:18:41

Books on-line !!!!

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-19 : 14:38:15

Cute...under what topic?

I'v searched a varity of combinations...constraints doesn't give examples either..

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-19 : 14:40:37

Try:

LIKE

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-19 : 14:43:39

Thanks dog...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-19 : 14:56:43

pffft...OMG

What a maroon....

What a gull-a-bull

Now I gotta see if DB2 V8 supports this...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources