我用T-SQL为SQL Server编写了以下查询
SELECT
CASE
WHEN ADDR_LINE_1 REGEXP '^[0-9]'
THEN SUBSTRING(ADDR_LINE_1,1,CHARINDEX(' ',ADDR_LINE_1))
ELSE NULL
END AS HOUSE_NUMBER
FROM CUSTOMER
我想要的是,如果列ADDR_LINE_1以一个数字开头,我想从中提取HOUSE_NUMBER。但现在我的查询给出了一个解析错误。如果我用LIKE替换单词REGEXP,解析错误就会消失,但是HOUSE_NUMBER总是得到NULL。我的查询的正确语法是什么?
您可以这样使用ISNUMERIC
和LEFT
。
SELECT
CASE WHEN ISNUMERIC(LEFT(ADDR_LINE_1, 1)) = 1
THEN SUBSTRING(ADDR_LINE_1, 1, CHARINDEX(' ', ADDR_LINE_1))
ELSE NULL
END AS HOUSE_NUMBER
FROM CUSTOMER
如何使用LIKE
SELECT
CASE
WHEN ADDR_LINE_1 Like '%[0-9]%'
THEN SUBSTRING(ADDR_LINE_1,1,CHARINDEX(' ',ADDR_LINE_1))
ELSE NULL
END AS HOUSE_NUMBER
FROM CUSTOMER