У меня есть столбец данных, который содержит процентный диапазон в виде строки, которую я хотел бы преобразовать в число, чтобы можно было легко сравнивать.
Возможные значения в строке:
'<5%'
'5-10%'
'10-15%'
...
'95-100%'
Я хотел бы преобразовать это в предложении select where только в первое число, 5, 10, 15 и т. Д., Чтобы я мог сравнить это значение с переданным значением «хотя бы это».
Я пробовал несколько вариантов подстроки, charindex, convert и replace, но я все еще не могу найти что-то, что работает во всех комбинациях.
Есть идеи?
Попробуй это,
SELECT substring(replace(interest , '<',''), patindex('%[0-9]%',replace(interest , '<','')), patindex('%[^0-9]%',replace(interest, '<',''))-1) FROM table1
Протестировано на моем конце, и это работает, это только моя первая попытка, так что вы можете оптимизировать его.
Вы можете преобразовывать данные char в другие типы char (конвертировать char (10) в varchar (10)), но вы не сможете преобразовать символьные данные в целочисленные данные из SQL.
Я не знаю, работает ли это в SQL Server, но в MySQL вы можете использовать несколько приемов для преобразования символьных данных в числа. Примеры из ваших образцов данных:
"<5%" => 0
"5-10%" => 5
"95-100%" => 95
теперь, очевидно, это проваливает ваш первый тест, но некоторых умных замен строк в начале строки будет достаточно, чтобы она заработала.
Один пример преобразования символьных данных в числа:
SELECT "5-10%" + 0 AS foo ...
Может не работать в SQL Server, но будущие поиски могут помочь нечетному пользователю MySQL :-D
Возможно, вам было бы лучше изменить <5%
и 5-10%
хранить 2 значения в 2 полях. Вместо того, чтобы хранить <5%
, вы должны хранить 0 и 5, и вместо этого вы 5-10%
получите 5 и 10. В итоге вы получите 2 столбца, один с именем lowerbound, а другой с именем upperbound, а затем просто проверьте значение >=
lowerbound AND value <
верхняя граница.
Вы можете сделать это на сервере SQL с помощью курсора. Если вы можете создать функцию CLR, чтобы вытянуть группы номеров, которые помогут. Это возможно в T-SQL, просто будет ужасно.
Создайте курсор для циклического перемещения по списку. Найдите первое число, если в их группе всего 1 группа, а затем верните его. В противном случае найдите вторую группу товаров.
если возвращена только 1-ая группировка элементов и ее первый элемент в списке устанавливает верхнюю границу. если возвращена только 1-я группа элементов, а последний элемент в списке устанавливает нижнюю границу. В противном случае установите 1-ую группу элементов на более низкую, а 2-й группы - на верхнюю границу.
Просто установите полученные значения обратно в таблицу
Проблема, с которой вы столкнулись, является признаком несоблюдения данных. В этом случае это выглядит просто непреднамеренно (Legacy), но здесь есть ссылка об этом.
Чтобы спроектировать себя из этого, создайте таблицу range_lookup:
Create table rangeLookup(
rangeID int -- or rangeCD or not at all
,rangeLabel varchar(50)
,LowValue int--real or whatever
,HighValue int
)
Взломать себе здесь несколько псевдо-шагов - это будет глубоко вложенный беспорядок.
normalize your input by replacing all your crazy charecters.
replace(replace(rangeLabel,"%",""),"<","")
--This will entail many nested replace statments.
Add a CASE and CHARINDEX to look for a space if there is none you have your number
else use your substring to take everything before the first " ".
-- theses steps are wrapped around the previous step.
@Martin: ваше решение работает.
Вот еще один, который я придумал на основе вдохновения от @mercutio
select cast(replace(replace(replace(interest,'<',''),'%',''),'-','.0') as numeric) test
from table1 where interest is not null
Это сложно, но для предоставленных вами тестовых случаев это работает. Просто замените @Test на столбец, который вы просматриваете в своей таблице.
DECLARE @TEST varchar(10)
set @Test = '<5%'
--set @Test = '5-10%'
--set @Test = '10-15%'
--set @Test = '95-100%'
Select CASE WHEN
Substring(@TEST,1,1) = '<'
THEN
0
ELSE
CONVERT(integer,SUBSTRING(@TEST,1,CHARINDEX('-',@TEST)-1))
END
AS LowerBound
,
CASE WHEN
Substring(@TEST,1,1) = '<'
THEN
CONVERT(integer,Substring(@TEST,2,CHARINDEX('%',@TEST)-2))
ELSE
CONVERT(integer,Substring(@TEST,CHARINDEX('-',@TEST)+1,CHARINDEX('%',@TEST)-CHARINDEX('-',@TEST)-1))
END
AS UpperBound