sql expression pattern matching

Question

I want to check for a pattern in sql such that if there is anything in that expression (or table cell) other than numeric it should return 1. If that whole cell has only numeric values it should return 0

eq:

case when '200290' like [anything other than numbers]
then 1
else o

What DB are you using? SqlServer? Could you use [ISNUMERIC()](http://msdn.microsoft.com/en-us/library/ms186272.aspx)? — Widor, Oct 07 '11 at 17:18
I am trying not to use any sql functions. The data base is teradata but a query in SQL server is also good — user708477, Oct 07 '11 at 17:24
see http://stackoverflow.com/questions/5064977/detect-if-value-is-number-in-mysql for a mysql answer — ax., Oct 07 '11 at 17:28

score 2 · Accepted Answer · answered Oct 07 '11 at 17:42

2

In SQL Server, you can use something like (I'm not writing the whole function for you):

DECLARE @t varchar(100) = '231321321321'

SELECT CASE WHEN PATINDEX('%[^0-9]%', @t) > 0 THEN 1
            ELSE 0 END

answered Oct 07 '11 at 17:42

JNK

63,321
15
122
138

@user708477 It's a common problem and a common solution. `ISNUMERIC()` doesn't work as expected so we have to come up with workarounds! – JNK Oct 07 '11 at 18:04
As of Teradata 13.10, you would need to replicate PATINDEX() functionality as a UDF. – Rob Paller Oct 10 '11 at 13:20

score 0 · Answer 2 · edited Sep 11 '15 at 10:30

SELECT CASE CHARACTERS(OTRANSLATE(<your_string>,'a1234567890','a')) 
WHEN 0 THEN 0 ELSE 1 END;

Ex.:

     -- BTEQ  Enter your SQL request or BTEQ command:
SELECT CASE CHARACTERS(OTRANSLATE('12345','a1234567890','a')) 
WHEN 0 THEN 0 ELSE 1 END;

* Query completed. One row found. One column returned. * Total elapsed time was 1 second.

<CASE  expression>
------------------
                 0

SELECT CASE CHARACTERS(OTRANSLATE('1a23b45c','a1234567890','a')) WHEN 0 THEN 0 ELSE 1 END;

* Query completed. One row found. One column returned. * Total elapsed time was 1 second.

<CASE  expression>
------------------
                 1

sql expression pattern matching

2 Answers2