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
--===== 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]%'
;
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 )
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.