问题描述
我正在寻找一种方法来替代 SQL Server 中 INSTR(...) 和 REPLACE(REGEXP_SUBSTR(...)) oracle 函数的使用.
I'm Looking for a way of replacing the use of INSTR(...) and REPLACE(REGEXP_SUBSTR(...)) oracle functions in SQL Server.
原始甲骨文:
SELECT
Name,
CASE
WHEN SUBSTR (NAME, 1, 2) = 'CG'
THEN SUBSTR (NAME,INSTR (NAME,'_',1,2)+ 1,LENGTH (NAME))
ELSE REPLACE (REGEXP_SUBSTR (NAME,'_[^_]+',1,2),'_','')
END AS OPT,
CASE
WHEN SUBSTR (NAME, 1, 2) = 'CG'
THEN SUBSTR (NAME,INSTR (NAME, '_',1,1) + 1, LENGTH (NAME))
ELSE REPLACE (REGEXP_SUBSTR (NAME,'_[^_]+',1,1),'_','')
END as Name2
更新示例输出:
+---------------------------------+----------------------+------------------------------+
| NAME | OPT | Name2 |
+---------------------------------+----------------------+------------------------------+
| AE 344592001H 6186694 | NULL | NULL |
| AE_161038002_6044777 | 6044777 | 161038002 |
| BC_VIVS_HNB011A_1WAM | HNB011A | VIVS |
| BC_56230A_30SP | 30SP | 56230A |
| CG_3334902_NETWK_ ACTLM_3334912 | NETWK_ ACTLM_3334912 | 3334902_NETWK_ ACTLM_3334912 |
| CG_3334574_HMO1_CORACT_3334575 | HMO1_CORACT_3334575 | 3334574_HMO1_CORACT_3334575 |
| CG_3207160_POSC_1502AH_3207161 | POSC_1502AH_3207161 | 3207160_POSC_1502AH_3207161 |
| UH_141015_RHM | RHM | 141015 |
| UH_127757_RIV | RIV | 127757 |
| UH 523725 RIV | NULL | NULL |
| BS_W0055785_C500_M0005672 | C500 | W0055785 |
+---------------------------------+----------------------+------------------------------+
我尝试查看 charindex 和 patindex 但没有任何结果,我认为原始的正则表达式一开始就超出了我的头脑.关于如何在 sql server 中模仿此逻辑的任何想法?
I tried looking at charindex and patindex but nothing panned out, I think the original regex expression is over my head to begin with. Any ideas on how I could mimic this logic in sql server?
推荐答案
MS SQL Server 2014 架构设置:
CREATE TABLE table_name ( name VARCHAR(50) );
INSERT INTO table_name
SELECT 'AE 344592001H 6186694' UNION ALL
SELECT 'AE_161038002_6044777' UNION ALL
SELECT 'BC_VIVS_HNB011A_1WAM' UNION ALL
SELECT 'BC_56230A_30SP' UNION ALL
SELECT 'CG_3334902_NETWK_ ACTLM_3334912' UNION ALL
SELECT 'CG_3334574_HMO1_CORACT_3334575' UNION ALL
SELECT 'CG_3207160_POSC_1502AH_3207161' UNION ALL
SELECT 'UH_141015_RHM' UNION ALL
SELECT 'UH_127757_RIV' UNION ALL
SELECT 'UH 523725 RIV' UNION ALL
SELECT 'BS_W0055785_C500_M0005672';
查询 1:
WITH Names ( lvl, name, remaining, idx ) AS (
SELECT 1,
name,
name,
CHARINDEX( '_', name )
FROM table_name
UNION ALL
SELECT lvl+1,
name,
SUBSTRING(remaining,idx+1,LEN(remaining)-idx),
CASE WHEN CHARINDEX( '_', remaining, idx+1 ) = 0
THEN 0
ELSE CHARINDEX( '_', remaining, idx+1 ) - idx
END
FROM Names
WHERE idx > 0
)
SELECT Name,
MAX( CASE WHEN lvl = 3 AND ( Name LIKE 'CG%' OR idx = 0 ) THEN remaining
WHEN lvl = 3 THEN SUBSTRING( remaining, 1, idx - 1 )
END ) AS OPT,
MAX( CASE WHEN lvl = 2 AND ( Name LIKE 'CG%' OR idx = 0 ) THEN remaining
WHEN lvl = 2 THEN SUBSTRING( remaining, 1, idx - 1 )
END ) AS Name2
FROM Names
GROUP BY Name
结果:
| Name | OPT | Name2 |
|---------------------------------|----------------------|------------------------------|
| AE 344592001H 6186694 | (null) | (null) |
| AE_161038002_6044777 | 6044777 | 161038002 |
| BC_56230A_30SP | 30SP | 56230A |
| BC_VIVS_HNB011A_1WAM | HNB011A | VIVS |
| BS_W0055785_C500_M0005672 | C500 | W0055785 |
| CG_3207160_POSC_1502AH_3207161 | POSC_1502AH_3207161 | 3207160_POSC_1502AH_3207161 |
| CG_3334574_HMO1_CORACT_3334575 | HMO1_CORACT_3334575 | 3334574_HMO1_CORACT_3334575 |
| CG_3334902_NETWK_ ACTLM_3334912 | NETWK_ ACTLM_3334912 | 3334902_NETWK_ ACTLM_3334912 |
| UH 523725 RIV | (null) | (null) |
| UH_127757_RIV | RIV | 127757 |
| UH_141015_RHM | RHM | 141015 |
查询 2:
CHARINDEX( expressionToFind, expressionToSerach[, startIndex] )
可用于查找单词中 _
的实例.
CHARINDEX( expressionToFind, expressionToSerach[, startIndex] )
can be used to find the instances of _
in the word.
CHARINDEX( '_', name )
将找到_
的第一个实例的索引.CHARINDEX( '_', name, CHARINDEX( '_', name ) + 1 )
将找到_
的第二个实例的索引或将返回0
如果没有两个_
字符.CHARINDEX( '_', name, CHARINDEX( '_', name, CHARINDEX( '_', name ) + 1 ) + 1)
将找到第三个实例的索引_
或者如果没有三个_
字符则返回0
.
CHARINDEX( '_', name )
will find the index of the first instance of an_
.CHARINDEX( '_', name, CHARINDEX( '_', name ) + 1 )
will find the index of the second instance of an_
or will return0
if there are not two_
characters.CHARINDEX( '_', name, CHARINDEX( '_', name, CHARINDEX( '_', name ) + 1 ) + 1)
will find the index of the third instance of an_
or will return0
if there are not three_
characters.
将其嵌套到内部选择中,您可以使用它在外部选择中获取适当的 SUBSTRING
,如下所示:
Nesting this into an inner select you can use it to get the appropriate SUBSTRING
s in an outer select like this:
SELECT name,
CASE WHEN idx2 > idx1 AND ( Name LIKE 'CG%' OR idx3 = 0 )THEN SUBSTRING( name, idx2 + 1, LEN( name ) )
WHEN idx3 > idx2 THEN SUBSTRING( name, idx2 + 1, idx3 - idx2 - 1 )
END AS OPT,
CASE WHEN name LIKE 'CG%' THEN SUBSTRING( name, idx1 + 1, LEN( name ) )
WHEN idx2 > idx1 THEN SUBSTRING( name, idx1 + 1, idx2 - idx1 - 1 )
END AS Name2
FROM (
SELECT name,
CHARINDEX( '_', name ) AS idx1,
CHARINDEX( '_', name, CHARINDEX( '_', name ) + 1 ) AS idx2,
CHARINDEX( '_', name, CHARINDEX( '_', name, CHARINDEX( '_', name ) + 1 ) + 1 ) AS idx3
FROM table_name
) t
结果:
| name | OPT | Name2 |
|---------------------------------|----------------------|------------------------------|
| AE 344592001H 6186694 | (null) | (null) |
| AE_161038002_6044777 | 6044777 | 161038002 |
| BC_VIVS_HNB011A_1WAM | HNB011A | VIVS |
| BC_56230A_30SP | 30SP | 56230A |
| CG_3334902_NETWK_ ACTLM_3334912 | NETWK_ ACTLM_3334912 | 3334902_NETWK_ ACTLM_3334912 |
| CG_3334574_HMO1_CORACT_3334575 | HMO1_CORACT_3334575 | 3334574_HMO1_CORACT_3334575 |
| CG_3207160_POSC_1502AH_3207161 | POSC_1502AH_3207161 | 3207160_POSC_1502AH_3207161 |
| UH_141015_RHM | RHM | 141015 |
| UH_127757_RIV | RIV | 127757 |
| UH 523725 RIV | (null) | (null) |
| BS_W0055785_C500_M0005672 | C500 | W0055785 |
这篇关于替换 SQL Server 中的 REGEXP_SUBSTR的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!