正文 

当前位置： 首页 > 问答 >

如何将单独列中冒号前后的单词拆分为sql中的行

     2023-05-08     69

关键词：

【中文标题】如何将单独列中冒号前后的单词拆分为sql中的行【英文标题】：how to split words before and after colon in separate columns to rows in sql 【发布时间】：2019-05-30 15:39:12 【问题描述】：

我在表中有一个列 [LongText]，它的行值是所有属性及其值的合并。下面是示例。 我们可以将单词前后的冒号分成两列，如预期结果所示？在 sql 2014 中需要它

Longtext

TYPE: SOLID WEDGE 1,SOLID WEDGE 2,  VALVE SIZE: 1 IN,  PRESSURE RATING: 800 LB,  CONNECTION TYPE: SOCKET WELD,  BONNET STYLE: BOLTED

预期输出为 2 列属性和值：

Attribute        | Value
----------------------------------------------
TYPE             | SOLID WEDGE 1,SOLID WEDGE 2
VALVE SIZE       | 1 IN
PRESSURE RATING  | 800 LB
CONNECTION TYPE  | SOCKET WELD
BONNET STYLE     | BOLTED

【问题讨论】：

处理这个问题的一个好方法是通过正则表达式。不幸的是，SQL Server 对正则表达式的支持很弱，所以它不是进行这种清理的最佳场所。在将数据导入 SQL Server 之前，您是否有机会使用其他工具清理数据？ 你好，蒂姆，你能举一些正则表达式的例子吗 如果这是一次性数据加载，您可以编写一个 while 循环，从字符串中删除第一个键/值对，将它们写入新表并继续直到字符串空的。或者，使用其他一些具有丰富正则表达式功能的编程语言来完成这项工作。 （如果这不是一次性数据加载，我不建议使用循环） STRING_SPLIT() 可能有用.. (docs.microsoft.com/en-us/sql/t-sql/functions/…) @Sai 你在回答部分检查了我的代码吗？ 【参考方案1】：

看看这是否有帮助。另外，如果您希望我解释代码，请告诉我。

IF EXISTS(SELECT 1 FROM SYS.OBJECTS WHERE NAME = 'fn_GetAttributeAndValueFromLongText' AND TYPE = 'TF')
BEGIN
    DROP FUNCTION dbo.fn_GetAttributeAndValueFromLongText
END
GO

CREATE FUNCTION dbo.fn_GetAttributeAndValueFromLongText ( @String_LongText VARCHAR(MAX) )
RETURNS @TBL_Attribute_Value TABLE 
(
    Attribute VARCHAR(MAX)
  , Value VARCHAR(MAX)
)
AS
BEGIN

    DECLARE @ATTR_VALUE_DELIMITER AS VARCHAR(MAX) = ':'

    WHILE (RTRIM(LTRIM(LEN(@String_LongText))) != 0)
    BEGIN
        DECLARE @String_ATTR VARCHAR(MAX)='', @String_VALUE VARCHAR(MAX)= ''

        SELECT @String_LongText = RTRIM(LTRIM(@String_LongText))

        SELECT @String_ATTR = SUBSTRING(@String_LongText, 1,CHARINDEX(':',@String_LongText)-1)
        SELECT @String_LongText = RIGHT(@String_LongText, LEN(@String_LongText)-(LEN(@String_ATTR)+1))

        IF @String_LongText LIKE '%'+@ATTR_VALUE_DELIMITER+'%'
        BEGIN
            SELECT @String_VALUE = LEFT(SUBSTRING(@String_LongText, 1, CHARINDEX(':', @String_LongText)-1), LEN(SUBSTRING(@String_LongText, 1, CHARINDEX(':', @String_LongText)-1)) - CHARINDEX(',', REVERSE(SUBSTRING(@String_LongText, 1, CHARINDEX(':', @String_LongText)-1))))
            SELECT @String_LongText = RIGHT(@String_LongText, LEN(@String_LongText)-(LEN(@String_VALUE)+1))
        END
        ELSE
        BEGIN
            SELECT @String_VALUE = @String_LongText
            SELECT @String_LongText = REPLACE(@String_LongText, @String_VALUE, '')
        END

        INSERT INTO @TBL_Attribute_Value ([Attribute], [Value])
        VALUES(RTRIM(LTRIM(@String_ATTR)), RTRIM(LTRIM(@String_VALUE))) 
    END

    RETURN
END

GO 

SELECT * FROM dbo.fn_GetAttributeAndValueFromLongText('TYPE: SOLID WEDGE 1,SOLID WEDGE 2,  VALVE SIZE: 1 IN,  PRESSURE RATING: 800 LB,  CONNECTION TYPE: SOCKET WELD,  BONNET STYLE: BOLTED')

【讨论】：

【参考方案2】：

开始使用 STRING_SPLIT()，类似于：

DECLARE @string varchar(max) = 'TYPE: SOLID WEDGE 1,SOLID WEDGE 2,  VALVE SIZE: 1 IN,  PRESSURE RATING: 800 LB .....';
DECLARE @output varchar(max) = '';
DECLARE @v varchar(max) = (SELECT TOP(1) value from string_split(@string,' '));
WHILE @v <> ''
BEGIN
    select @v;
    SET @string = (SELECT ltrim(substring(@string,LEN(@v)+1,1024)));
    select @string;
    SET @v = (SELECT TOP(1) value from string_split(@string,' '));
END

给出：

正如@Tim 所说，不能保证上述方法的顺序是正确的。

所以，第二次尝试?：

DECLARE @string varchar(max) = 'TYPE: SOLID WEDGE 1,SOLID WEDGE 2,  VALVE SIZE: 1 IN,  PRESSURE RATING: 800 LB,  CONNECTION TYPE: SOCKET WELD,  BONNET STYLE: BOLTED';
SELECT * FROM string_split(REPLACE(@string,'  ','#'),'#');

这希望（='does-not-check'）原始字符串中没有“#”字符。

【讨论】：

这个答案可能无法正常工作，因为STRING_SPLIT不保证会保持单词的顺序。

如何将存储在列中的 JSON 数组中的每个元素的行拆分为一行？

】如何将存储在列中的JSON数组中的每个元素的行拆分为一行？【英文标题】：HowcanIsplitrowsintoonerowforeachelementinaJSONarraystoredinacolumn?【发布时间】：2020-03-1715:16:40【问题描述】：好的，所以，这很难解释，但我会试一试。Google没有... 查看详情

将一行拆分为三个单独的行

】将一行拆分为三个单独的行【英文标题】：splitarowintothreeseparaterows【发布时间】：2020-05-0518:35:48【问题描述】：我需要将一个sql行拆分为3个单独的行，下面是该行：Trim_Master_idBatch_idmobius_A_start_runtimemobius_A_end_runtimemobius_A_runtime... 查看详情

如何在 BigQuery SQL 中将字符串列拆分为多行单个单词和单词对？

】如何在BigQuerySQL中将字符串列拆分为多行单个单词和单词对？【英文标题】：HowdoIsplitastringcolumnintomultirowsofsinglewords&wordpairsinBigQuerySQL?【发布时间】：2018-03-2114:53:36【问题描述】：我正在尝试（未成功）将GoogleBigQuery中的字... 查看详情

(Oracle) SQL 中的正则表达式将日期/时间拆分为单独的日期和时间列

】(Oracle)SQL中的正则表达式将日期/时间拆分为单独的日期和时间列【英文标题】：RegularExpressionsin(Oracle)SQLtosplitdate/timeintoseparatedateandtimecolumns【发布时间】：2020-03-1617:09:13【问题描述】：我正在尝试使用正则表达式从包含日期和... 查看详情

如何将一列中的单词拆分然后在Python中将单词整合在一起，即二维列表到一维列表？

】如何将一列中的单词拆分然后在Python中将单词整合在一起，即二维列表到一维列表？【英文标题】：HowtosplitthewordsinonecolumnthenintegratethewordstogetherinPython,i.e.,twodimensionallisttoonedimensionallist?【发布时间】：2019-12-1415:07:22【问题描述... 查看详情

为啥 Safari 将某些短语拆分为单独的行？

】为啥Safari将某些短语拆分为单独的行？【英文标题】：WhydoesSafarisplitcertainphrasesontoseparatelines?为什么Safari将某些短语拆分为单独的行？【发布时间】：2019-12-1303:09:54【问题描述】：替代问题名称：为什么Safari认为SourceSansPro中的... 查看详情

SQL 将列值拆分为 Netezza 中的行

...etezza【发布时间】：2018-10-1716:20:05【问题描述】：我在一列中有以下方式的数据。列中的数据由两个空格分隔。4EGC6CCC6DE6MMC6LLL3BCC3我需要把它分成如下。我尝试使用REGEXP_SUBSTR来执行此操作，但看起来它不在SQL工具包中。有什么... 查看详情

如何将数据框单元格内的列表分解为单独的行

】如何将数据框单元格内的列表分解为单独的行【英文标题】：HowtoexplodealistinsideaDataframecellintoseparaterows【发布时间】：2015-12-0418:20:58【问题描述】：我希望将包含列表的pandas单元格转换为每个值的行。所以，拿着这个：如果我... 查看详情

SQL - 将字段的数据分解为单独的行

...以下内容：Field1|Field2|Field3------------------------Foo|Bar|ABCD我如何将这些数据分解为：Field1|Field2|Field3--------------- 查看详情

如何使用类型化数据集将多值列拆分为单独的行？

】如何使用类型化数据集将多值列拆分为单独的行？【英文标题】：Howtosplitmulti-valuecolumnintoseparaterowsusingtypedDataset?【发布时间】：2017-04-2120:19:43【问题描述】：我面临如何将多值列（即List[String]）拆分为单独行的问题。初始数... 查看详情

将嵌入的 CSV 文本拆分为单独的行

】将嵌入的CSV文本拆分为单独的行【英文标题】：SplitEmbeddedCSVTextIntoSeparateRows【发布时间】：2019-07-1101:31:45【问题描述】：我正在尝试将逗号分隔的数据拆分为单独的列（使用SQLServer2008）。我在这里看到过类似的问题，但就我... 查看详情

pandas：如何将列中的文本拆分为多行？

】pandas：如何将列中的文本拆分为多行？【英文标题】：pandas:HowdoIsplittextinacolumnintomultiplerows?【发布时间】：2013-06-1114:15:59【问题描述】：我正在处理一个大的csv文件，倒数第二列有一个我想用特定分隔符分割的文本字符串。我... 查看详情

如何在没有交叉产品的情况下从 BigQuery 中的两列中取消嵌套两个列表，作为单独的行

】如何在没有交叉产品的情况下从BigQuery中的两列中取消嵌套两个列表，作为单独的行【英文标题】：HowtounnesttwolistsfromtwocolumnsinBigQuerywithoutcrossproduct,asindividualrows【发布时间】：2020-01-2900:47:54【问题描述】：我在BigQuery中有一个... 查看详情

如何将字符串中的“\t”拆分为两个单独的字符“\”和“t”？ （如何拆分转义序列？）[重复]

】如何将字符串中的“\\\\t”拆分为两个单独的字符“\\\\”和“t”？（如何拆分转义序列？）[重复]【英文标题】：Howtosplit"\\t"inastringtotwoseparatecharactersas"\\"and"t"?(HowtosplitEscapeSequence?)[duplicate]如何将字符串... 查看详情

C ++函数将字符串拆分为单词

...在C++中编写一个函数，将我的字符串测试拆分为数组中的单独单词。我似乎无法正确循环中的内容...有人有任何想法吗？它应该打印“this”voidapp::split()stringtest="thisismytestingstring.";char*tempLin 查看详情

如何拆分列包括键和值到postgres中的单独列中

】如何拆分列包括键和值到postgres中的单独列中【英文标题】：Howtobreakapartacolumnincludeskeysandvaluesintoseparatecolumnsinpostgres【发布时间】：2020-03-2913:38:10【问题描述】：我是postgres的新手，基本上没有经验。我有一个包含键和值的列... 查看详情

如何使用正则表达式拆分列以将尾随 CAPS 移动到单独的列中？

】如何使用正则表达式拆分列以将尾随CAPS移动到单独的列中？【英文标题】：HowcanIsplitcolumnswithregextomovetrailingCAPSintoaseparatecolumn?【发布时间】：2020-04-2500:59:22【问题描述】：我正在尝试使用正则表达式拆分列，但似乎无法正确... 查看详情

如何将两个单独表中的两列拆分为视图中的多行？

】如何将两个单独表中的两列拆分为视图中的多行？【英文标题】：HowdoIsplittwocolumnsintwoseparatetablesintojoinedmultiplerowsinaview?【发布时间】：2016-03-2910:01:59【问题描述】：我有两个单独的表格，每个表格都包含字符分隔的单元格。... 查看详情