CHill60
Решение 1-это лучший совет, который вы получите, но в этом решении я буду предполагать, что у вас уже есть эти данные и вы хотите преобразовать их в более разумный формат, предложенный в решении 1.
Сначала изучите данные - на первый взгляд кажется, что существует 7 или более различных форматов, но числа, которые вас интересуют, находятся только в двух разных форматах ... больше ничего нет в столбце, например, ваша строка 3 0.3583, Или они окружены скобками (...).
Если вы окружены скобками, то у вас есть только две версии этого - только цифры, например, ваша строка 1 8.92 mm (0.3510) или за ним следует "в", Например, ваша строка 5 #77 (0.0180 in)
Все остальное в данных игнорируется, поэтому то, что вы пытаетесь сделать, это
1. Удалите все, что не связано с требуемым номером
2. Удалите в скобках все, что не является числом
3. Снимите скобки
Когда вы подходите к проблемам такого рода, очень важно сделать этот предварительный анализ, чтобы вы могли быть уверены, что вы охватываете все возможные варианты.
Вы можете сделать шаг 1 с sql следующим образом
select id, s.[value]
FROM @test
CROSS APPLY string_split (dat, '(') s
WHERE s.value LIKE '%)%'
UNION
select id, dat
FROM @test
WHERE dat NOT LIKE '%(%'Моменты, которые следует отметить:
а) если вы используете версию SQL до 2016 года, вам придется написать свой собственный UDF, чтобы выполнить разделение строк. Есть сотни примеров, доступных через вашу любимую поисковую систему.
б) обратите внимание, что в первом запросе я просто ищу цифры в скобках. Я игнорирую все, что не было заключено в скобки, проверяя наличие закрывающей скобки
в) второй запрос ищет шаблон, где столбец содержит только наше правило чисел.
Пока что мы получаем такие результаты:
id [value]
1 0.3510)
2 1.2210)
3 0.3583
4 1.0312)
5 0.0180 in)
6 0.2770)
7 0.1719)
8 0.6562 in)
Таким образом, нам все еще нужно избавиться от закрывающей скобки и "in", что мы можем сделать следующим образом
;with cte as
(
select id, REPLACE(REPLACE(s.value, ')',''), ' in','') AS [value]
FROM @test
CROSS APPLY string_split (dat, '(') s
WHERE s.value LIKE '%)%'
UNION
select id, dat
FROM @test
WHERE dat NOT LIKE '%(%'
)
SELECT id, [value]
from cteЗдесь очень важно отметить, что эти значения являются строками - возможно, вам придется преобразовать их в числа, прежде чем они будут использованы.
Теперь я собираюсь вернуться к решению 1, где @OriginalGriff указывает
Цитата:
вы найдете входные данные, которые не соответствуют ни одному из приведенных примеров, поэтому вам придется часто менять сложный SQL-код для обнаружения и добавления новых случаев.
К счастью, вы последуете его совету, так что это будет одноразовое упражнение, а не частое явление. Однако вам нужно будет изучить ваши преобразованные данные, чтобы убедиться, что вы захватили все.
РЕДАКТИРОВАТЬ: Я просто изменил свои тестовые данные, чтобы проверить наличие других проблем - обратите внимание на новый формат в последнем значении
declare @test table (id int identity(1,1), dat nvarchar(50))
insert into @test (dat) values
('8.92 mm (0.3510)' ),
('31.00 mm (1.2210)'),
('0.3583'),
('1-1/32" (1.0312)'),
('#77 (0.0180 in)' ),
('J (0.2770)'),
('11/64" (0.1719)'),
('21/32 in (0.6562 in)')
,('0.3583 in')Это привело к тому, что мой код выше выдал ошибку
Msg 8114, Уровень 16, Состояние 5, Строка 28
Ошибка преобразования типа данных nvarchar в числовой.
Этого можно избежать (в данном случае), выполнив замену вне cte, т. е.
;with cte as
(
select id, s.[value]
FROM @test
CROSS APPLY string_split (dat, '(') s
WHERE s.value LIKE '%)%'
UNION
select id, dat
FROM @test
WHERE dat NOT LIKE '%(%'
)
SELECT id, REPLACE(REPLACE([value], ')',''), ' in','')
from cteНо это действительно делает точку зрения, которую сделал @OriginalGriff и которую я процитировал выше!