Как разделить слова до и после двоеточия в отдельных столбцах на строки в sql
У меня есть столбец [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
Привет Тим, не могли бы вы привести несколько примеров регулярного выражения
Если это однократная загрузка данных, вы можете написать цикл while, который удаляет из строки первые пары ключ/значение, записывает их в новую таблицу и продолжает до тех пор, пока строка не станет пустой. В качестве альтернативы для выполнения этой работы используйте какой-либо другой язык программирования с богатыми функциями регулярных выражений. (Если это нет однократная загрузка данных, я не рекомендую использовать цикл)
STRING_SPLIT() может пригодиться.. (docs.microsoft.com/en-us/sql/t-sql/functions/…)
@Sai Вы проверили мой код в разделе ответов?
Ответы 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 не гарантирует, что порядок слов будет сохранен.
Посмотрите, может ли это помочь. Кроме того, дайте мне знать, если вы хотите, чтобы я объяснил код.
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')
Один из хороших способов справиться с этим — использовать регулярные выражения. К сожалению, SQL Server имеет слабую поддержку регулярных выражений, поэтому это не лучшее место для такой очистки. Есть ли шанс, что вы могли бы использовать какой-либо другой инструмент для очистки ваших данных перед переносом их в SQL Server?