SQL Server - 选择最后一个连字符之后的所有字符的子字符串 [英] SQL Server - select substring of all characters following last hyphen

问题描述

我正在使用产品数据库，尝试从组合的 ID/颜色代码列中提取产品颜色，其中颜色代码始终是列中最后一个连字符后面的字符串.问题是连字符的数量、产品 ID 和颜色代码都可能不同.

I am working with a database of products, trying to extract the product color from a combined ID/color code column where the color code is always the string following the last hyphen in the column. The issue is that the number of hyphens, product ID, and color code can all be different.

这里有四个例子:

ABC123-001
BCD45678-0165
S-XYZ999-M2235
A-S-ABC123-001

在这种情况下，颜色代码将是 001、0165、M2235 和 001.将这些选择到他们自己的列中的最佳方法是什么?

The color codes in this case would be 001, 0165, M2235, and 001. What would be the best way to select these into their own column?

推荐答案

我认为以下内容可以满足您的需求:

I think the following does what you want:

select right(col, charindex('-', reverse(col)) - 1)

如果值中没有连字符，请使用 case:

In the event that you might have no hyphens in the value, then use a case:

select (case when col like '%-%'
             then right(col, charindex('-', reverse(col)) - 1)
             else col
        end)

这篇关于SQL Server - 选择最后一个连字符之后的所有字符的子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！