在 SELECT 语句中提取分隔值右侧的字符

2021-12-19 00:00:00 sql delimiter select sql-server

我需要提取连字符右侧的所有字符作为选择语句的一部分.选择中会有其他列.在下面的查询中，从第二列中选择了正确的三个字符.我如何在分隔符的右侧提取无限数量的字符——在我的例子中是一个连字符?我可以使用正确的功能吗?我需要使用其他功能吗?

I need to extract all the characters to the right of a hyphen as part of a select statement. There will be other columns in the select. In the below query, the right three characters are selected from the second column. How would I extract an indefinite number of characters to the right of a delimiter – in my case a hyphen? Can I use the right function? Do I need to use another function?

Select column1, right(column2,3) as extracted, column3
From myTable

我使用的是 SQL Server 2008.

I am using SQL Server 2008.

推荐答案

这个问题有一个特定于数据库的答案.

This question has a database specific answer.

如果使用 SQL Server:

If using SQL Server:

SELECT column1
     , RIGHT(column2,CHARINDEX('-',REVERSE(column2))-1) as extracted
     , column3 
FROM myTable

您可以添加 CASE 语句或使用 NULLIF() 以防连字符不总是存在:

You can add a CASE statement or use NULLIF() in case the hyphen isn't always present:

SELECT column1
     , CASE WHEN column2 LIKE '%-%' THEN RIGHT(column2,CHARINDEX('-',REVERSE(column2))-1) 
           END as extracted
     , column3 
FROM myTable

或者:

SELECT column1
     , RIGHT(column2,NULLIF(CHARINDEX('-',REVERSE(column2)),0)-1) as extracted
     , column3 
FROM myTable

如果使用 MySQL，只需将 CHARINDEX() 更改为 LOCATE().我相信 Oracle 它是 INSTR() 并且前两个参数被切换，首先是您正在搜索的字符串，然后是您正在搜索的字符串.

If using MySQL just change CHARINDEX() to LOCATE(). I believe Oracle it's INSTR() and the first two parameters are switched, first it's the string you're searching in, then the string you're searching for.

相关文章