How can I write a select statement to select only integers (and nothing more) from a char column in SQL Server. For example, my table name is POWDER with 2 columns, ID (int) and Name(char (5))
ID Name
-- ----------
1 AXF22
2 HYWWW
3 24680
4 8YUH8
5 96635
I want to be able to select only those rows that contain an integer and nothing more (ID 3 and ID 5 in this example)
If I try:
SELECT *
FROM POWDER
WHERE Name LIKE '[0-9]%'
开发者_如何转开发
...it will return:
ID Name
-- ----------
3 24680
4 8YUH8
5 96635
Any ideas how to get the rows containing just integers?
SELECT * FROM POWDER WHERE IsNumeric(Name) = 1
IsNumeric returns 1 for some other characters that are valid in numbers, such as + and - and $ but for your input you should be fine.
Try this:
SELECT * FROM Table WHERE Name LIKE '[0-9]%%'
To avoid issues with ISNUMERIC and all spaces, -, +, . etc, use the fact that the column is char(5)
SELECT *
FROM POWDER
WHERE Name LIKE '[0-9][0-9][0-9][0-9][0-9]'
Edit: for any number of characters. Double negative...
SELECT *
FROM POWDER
WHERE Name NOT LIKE '%[^0-9]%'
Use positive and negative checks to make sure we have an integer: It must contain a digit. Only digits and spaces are allowed. No spaces are allowed between digits.
SELECT *
FROM POWDER
WHERE Name LIKE '%[0-9]%'
AND Name NOT LIKE '%[^0-9 ]%'
AND Name NOT LIKE '%[0-9]% %[0-9]%'
Try:
SELECT *
FROM POWDER
WHERE Name patindex ('%[a-z]%',name) != 0
The last one is the best,kind of works really really well.
SELECT * FROM POWDER
WHERE Name LIKE '%[0-9]%'
AND Name NOT LIKE '%[^0-9 ]%'
AND Name NOT LIKE '%[0-9]% %[0-9]%'
精彩评论