如何使用 tSQL 匹配和删除 2 个字符串之一之后的所有内容? [英] How to use tSQL to match and remove everything after either of 2 strings?

问题描述

如何使用 tSQL 查找两个字符串之一，如果它们存在，则返回找到字符串之前的所有内容?

在 ETL 过程中，我们如何从源中获取列，识别字符串 ?uniquecode= 或 /uniquecode= 并因此删除这些字符串以及之后的所有其他内容它们，在接收列的 SELECT 语句中?即匹配下面的期望结果.

关于

演示

How can I use tSQL to find one of two strings, and if they exist, return everything before found string?

In an ETL process, how would we take the column from source, identify the strings ?uniquecode= OR /uniquecode= and therefore remove those, and everything else after them, in the SELECT statement for the sink column? i.e. matching desired outcome below.

On this SO question I was provided with a solution that finds ?uniquecode= successfully. I just need to find a way to modify it to also look for /uniquecode=

SELECT
CASE WHEN CHARINDEX('?uniquecode=', SourcePageURL) > 0
     THEN SUBSTRING(SourcePageURL,
                    1,
                    CHARINDEX('?uniquecode=', SourcePageURL) - 1)
     ELSE SourcePageURL END AS new_source
FROM sql_test;

解决方案

You may modify your current query as follows:

SELECT
    CASE WHEN SourcePageURL LIKE '%?uniquecode=%'
         THEN SUBSTRING(SourcePageURL,
                        1,
                        CHARINDEX('?uniquecode=', SourcePageURL) - 1)
         WHEN SourcePageURL LIKE '%/uniquecode%'
         THEN SUBSTRING(SourcePageURL,
                        1,
                        CHARINDEX('uniquecode=', SourcePageURL) - 1)
         ELSE SourcePageURL END AS new_source
FROM sql_test;

Demo

这篇关于如何使用 tSQL 匹配和删除 2 个字符串之一之后的所有内容?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！