在SELECT语句中的分隔值右侧提取字符 [英] Extract characters to the right of a delimited value in a SELECT statement

问题描述

作为select语句的一部分，我需要提取连字符右侧的所有字符.选择中将有其他列.在下面的查询中，从第二列中选择了右边的三个字符.如何提取定界符右边的无限个字符(在我的情况下为连字符)?我可以使用正确的功能吗?我需要使用其他功能吗?

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.

这篇关于在SELECT语句中的分隔值右侧提取字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！