How to make pattern for cases Numbers and characters on sql server 2012

Transact-SQL
,
1

I work on SQL server 2012 I face issue I can't make select statement for 4 cases
exist on temp table

create table #temp
(
Numbers nvarchar(50)
)
insert into #temp(Numbers) values
('>1000'),
('abc1000'),
('5000'),
('ABCD'),
('<=6000')

I need to make select statement return numbers only as 5000 .

i need to make select statement return text only as ABCD .

I need to make select statement return comparison operator with numbers as <=6000 and >1000 only

I need to make select statement return text and numbers as abc1000 only

so How to make pattern for all cases above ?

I tried :

SELECT * FROM #temp WHERE PATINDEX('%[0-9]%', Numbers) != 0 to get 5000 only but not work
SELECT * FROM #temp WHERE PATINDEX('%[a-z]%', Numbers) != 0 to get ABCD only
but not worK

and other cases i can't do any pattern for it

3

can you please help me on cases pattern above

4

hi

please try this

SELECT * FROM #temp WHERE ISNUMERIC(Numbers) = 1

image

5

hi

please see this for Alphabets Only

SELECT * FROM #temp WHERE Numbers LIKE '[A-Za-z]%' and Numbers not Like '%[0-9]%'

image

6

Hi Ahmed

What happened

No response

7

Try the following and get a nice, big surprise...

SELECT ISNUMERIC('2E3');

Surprise!!! Never use ISNUMERIC as an "IsAllDigits" function because it's not.

8

Just dropping in my 2 cents here...

--===== Numeric Digits Only
 SELECT Numbers 
   FROM #temp
   WHERE Numbers NOT LIKE N'%[^0-9]%'
;
--===== Alpha Characters Only
 SELECT Numbers
   FROM #temp
  WHERE Numbers NOT LIKE N'%[^a-zA-Z]%'
;
--===== Comparison operators and digits only
 SELECT Numbers
   FROM #temp
  WHERE Numbers NOT LIKE N'%[^<=>0-9]%'
    AND Numbers LIKE N'%[<=>]%'
;
--===== Alphanumeric only
 SELECT Numbers
   FROM #temp
  WHERE Numbers NOT LIKE N'%[^0-9a-zA-Z]%'
    AND Numbers LIKE N'%[a-zA-Z]%'
    AND Numbers LIKE N'%[0-9]%'
;
9

Thanks Jeff .. for pointing it out

I recollect reading about this point .. on this forum ..years ago ..

So hard to remember so many gotchas ..

Thanks again Jeff
:+1: :+1:

10

In my experience Jeff

This would have resulted in a bug
.. that would have caused a lot of pain to figure out

Obviously i would think its working .. when its not
i would not be looking for it .. LOT of heart ache
i would start checking from Line 1 to Line 1000

11

If you think that one is bad, try this...

SELECT ISNUMERIC(',,,,,,');

ISNUMERIC is definitely NOT an ISALLDIGITS function. Basically, it'll take ANY string that SQL Server can somehow convert to ANY of the numeric datatypes including engineering notation, scientific notation, MONEY (with and without currency symbols and commas, etc) and a wealth of other stuff that's convertible.

You can read more about it at the following article (you just had to know I'd have such a thing :smiley: )

12

fix it at the source. playing around with PATINDEX and what not, parsing string will come to bite you in the bootay eventually and not guaranteed to catch all.

13

thank you very much it solved