如何在T-SQL中清理字符串并提取数字后缀
如何在T-SQL中清理字符串并提取数字后缀
提问于 2019-05-23 21:17:40
我有一个由名字组成的字符串，在大多数情况下，它有一个后缀，末尾有一个或两个数字。这个数字后缀应该从名称中去掉。一个数字代表一个状态，应该提取出来。如果有两个数字，就是右数第二个数字，如果有一个数字，就是右数第一个数字。这些数字由下划线分隔。也可以在名称中使用下划线。结果应该是一个包含clearname和提取状态的列。
我试图用标准的字符串函数来解决这个问题，比如Substring、Charindex、Patindex、LEN和son等等。但我的方法很快就变得非常笨重，而且很难维护。我想知道是否有一个优雅的解决方案，具有通常的SQl-Server功能(如果可能的话，不需要安装regex的额外功能)。
SELECT _data.myStr
-- , ... AS clearname /*String cleaned from number_postfixes*/
-- , ... AS Status /*second number from the right*/
FROM (
SELECT 'tree_leafs_offer_2_1' AS myStr --clearname: tree_leafs_offer; cut off: _2_1; extracted status: 2
UNION
SELECT 'tree_leafs_offer_2_10' AS myStr --clearname: tree_leafs_offer_2_10; cut off: _2_10; extracted status: 2
UNION
SELECT 'tree_leafs_offer_2_2' AS myStr --clearname: tree_leafs_offer; cut off: _2_2; extracted status: 2
UNION
SELECT 'tree_leafs_offer_1150_1' AS myStr --clearname: tree_leafs_offer; cut off: _1150_1; extracted status: 1150
UNION
SELECT 'tree_leafs_offer_1150_10' AS myStr --clearname: tree_leafs_offer; cut off: _1150_10; extracted status: 1150
UNION
SELECT 'builder_bundle_less_xl_1' AS myStr --clearname: builder_bundle_less_xl; cut off: _1; extracted status: 1
UNION
SELECT 'builder_bundle_less_xl_10' AS myStr --clearname: builder_bundle_less_xl; cut off: _10; extracted status: 10
UNION
SELECT 'static_components_wolves_10_4' AS myStr --clearname: static_components_wolves; cut off: _10_4; extracted status: 4
UNION
SELECT 'coke_0_boring_components_bundle_grant_1' AS myStr --clearname: oke_0_boring_components_bundle_grant; cut off: _1; extracted status: 1
UNION
SELECT 'coke_0_soccer18_end_1_4h_101' AS myStr --clearname: coke_0_soccer18_end_1_4h; cut off: _101; extracted status: 101
UNION
SELECT 'coke_0_late_downsell_bundle_high_114' AS myStr --clearname: coke_0_late_downsell_bundle_high; cut off: _114; extracted status: 114
UNION
SELECT 'itembundle_mine_bundle_small' AS myStr --clearname: itembundle_mine_bundle_small; cut off: <nothing>; extracted status: NULL
) AS _dataAs-Is Result:
-----------------
myStr:
---------------------------------------
builder_bundle_less_xl_1
builder_bundle_less_xl_10
coke_0_boring_components_bundle_grant_1
coke_0_late_downsell_bundle_high_114
coke_0_soccer18_end_1_4h_101
itembundle_mine_bundle_small
static_components_wolves_10_4
tree_leafs_offer_1150_1
tree_leafs_offer_1150_10
tree_leafs_offer_2_1
tree_leafs_offer_2_10
tree_leafs_offer_2_2
To-Be Result (two new columns):
-------------------
clearname: |Status
----------------------------------------------
builder_bundle_less_xl | 1
builder_bundle_less_xl | 10
coke_0_boring_components_bundle_grant | 1
coke_0_late_downsell_bundle_high | 114
coke_0_soccer18_end_1_4h | 101
itembundle_mine_bundle_small |NULL
static_components_wolves | 10
tree_leafs_offer |1150
tree_leafs_offer |1150
tree_leafs_offer | 2
tree_leafs_offer | 2
tree_leafs_offer | 2回答 1
Stack Overflow用户
发布于 2019-05-23 22:46:50
一种可能的方法是使用SQL Server 2016+的字符串替换和JSON功能。每一行都被颠倒并转换成一个有效的JSON数组(例如，'tree_leafs_offer_2_1'被转换为'["1","2","reffo","sfael","eert"]' )。然后，您可以使用JSON_VALUE(<json_array>, '$[0]')、JSON_VALUE(<json_array>, '$[1]')和TRY_CONVERT()轻松检查第一项和第二项是否为有效数字。如果右边最多有两个数字，这将会起作用。
输入：
CREATE TABLE #Data (
myStr varchar(max)
)
INSERT INTO #Data
(MyStr)
VALUES
('tree_leafs_offer_2_1'),
('tree_leafs_offer_2_10'),
('tree_leafs_offer_2_2'),
('tree_leafs_offer_1150_1'),
('tree_leafs_offer_1150_10'),
('builder_bundle_less_xl_1'),
('builder_bundle_less_xl_10'),
('static_components_wolves_10_4'),
('coke_0_boring_components_bundle_grant_1'),
('coke_0_soccer18_end_1_4h_101'),
('coke_0_late_downsell_bundle_high_114'),
('itembundle_mine_bundle_small')T-SQL：
SELECT
LEFT(myStr, LEN(myStr) - CHARINDEX('_', REVERSE(myStr))) as ClearName,
REVERSE(LEFT(REVERSE(myStr), CHARINDEX('_', REVERSE(myStr)) - 1)) AS Status
FROM (
SELECT
CASE
WHEN
TRY_CONVERT(int, REVERSE(JSON_VALUE(CONCAT('["', REPLACE(STRING_ESCAPE(REVERSE(MyStr), 'json'), '_', '","'), '"]'), '$[1]'))) IS NULL AND
TRY_CONVERT(int, REVERSE(JSON_VALUE(CONCAT('["', REPLACE(STRING_ESCAPE(REVERSE(MyStr), 'json'), '_', '","'), '"]'), '$[0]'))) IS NULL
THEN CONCAT(myStr, '_0')
WHEN
TRY_CONVERT(int, REVERSE(JSON_VALUE(CONCAT('["', REPLACE(STRING_ESCAPE(REVERSE(MyStr), 'json'), '_', '","'), '"]'), '$[1]'))) IS NULL AND
TRY_CONVERT(int, REVERSE(JSON_VALUE(CONCAT('["', REPLACE(STRING_ESCAPE(REVERSE(MyStr), 'json'), '_', '","'), '"]'), '$[0]'))) IS NOT NULL
THEN MyStr
ELSE LEFT(myStr, LEN(myStr) - CHARINDEX('_', REVERSE(myStr)))
END AS myStr
FROM #Data
) fixed
ORDER BY MyStr输出：
----------------------------------------------
ClearName Status
----------------------------------------------
builder_bundle_less_xl 1
builder_bundle_less_xl 10
coke_0_boring_components_bundle_grant 1
coke_0_late_downsell_bundle_high 114
coke_0_soccer18_end_1_4h 101
itembundle_mine_bundle_small 0
static_components_wolves 10
tree_leafs_offer 1150
tree_leafs_offer 1150
tree_leafs_offer 2
tree_leafs_offer 2
tree_leafs_offer 2页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56276151
复制相关文章
相似问题