Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как нецифры превратить в ноль одним SELECT'ом?  [new]
Serg Alex Lad
Member

Откуда:
Сообщений: 6
Никто не подскажет как одним запросом все нецифры превратить в ноль в конструкции вида:

select str from(
select '0' as str union
select '12' union
select '345' union
select '6789' union
select '1й' union
select '2я' union
select '2003г'
) a

?
16 сен 09, 17:52    [7669424]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5499
Блог
Например, так:
select case when str like '%[^0-9]%' then 0 else convert(int, str) end as str 
from(
select '0' as str union
select '12' union
select '345' union
select '6789' union
select '1й' union
select '2я' union
select '2003г'
) a
Хотя могли бы и поиском воспользоваться - тема популярная... :(
16 сен 09, 17:57    [7669475]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
Serg Alex Lad
Member

Откуда:
Сообщений: 6
DeColo®es,

Не то, у вас все строки где есть нецифры превращаются в ноль, а мне кажный символ надо превратить в ноль а цифры оставить, похожее находил, но там либо совсем не то, либо что то монстроидальное с применением непонятных мне элементов (судя по всему какой то встроенный язык MS SQL, я с ANSI SQL более знаком).
16 сен 09, 18:05    [7669533]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
автор
либо что то монстроидальное с применением непонятных мне элементов (судя по всему какой то встроенный язык MS SQL, я с ANSI SQL более знаком).


гМ... Вы задаете вопрос на форуме MS SQL. Почему Вы ожидаете, что Вам ответят на "голом" ANSI? какая у Вас СУБД?
16 сен 09, 18:10    [7669560]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Автор, а на таких данных:
select str from(
select '-345' as str union
select '1e2' union
select '3.14'
) a
какой должен быть результат?
(это всё числа, если что...)

Расскажите, пожалуйста, подробнее, какую задачу Вы решаете?
16 сен 09, 18:16    [7669591]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
Serg Alex Lad
Member

Откуда:
Сообщений: 6
pkarklin,

Ну что вы, голый ANSI мне не нужно :)

У меня СУБД MS SQL 2005.

В общем передо мной периодически встают задачи по очисте строк от различных символов, так например в поле ГОД удалить все окончания, оставиви только цифры. В поле инвентарный номер все буквы просто заменить на нули, т.е. штука вида АА1234 что бы привелась к такому виду: 001234.

А как решить такие задачки, я не могу понять. Знаю что можно воспользоваться конструкцией типа:

replace(str,substring(str, patindex('%[^0-9]%', str),1),'0')

Но эта конструкция заменит один символ и отвалит, а как заменить (или удалить) все символы?
16 сен 09, 18:20    [7669609]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
replace(......replace(.........),'а',0)......),'z', 0)
или написать свою сборку с регулярными віражениями
-------------------------------------
Jedem Das Seine
16 сен 09, 18:28    [7669662]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Serg Alex Lad
периодически встают задачи
иногда и мне приходится пожалеть, что в MS SQL нету функции, похожей на оракловую TRANSLATE

Но есть добрые люди, которые предлагают свои аналоги
Ели автор поищет, наверняка найдет еще
16 сен 09, 18:30    [7669670]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
Serg Alex Lad
Member

Откуда:
Сообщений: 6
Паганель,

TRANSLATE заменяет известное на известное :-) а я хз, что мне подсунут :-) .

Хм, неохота с фунциями завязываться, но если не найду другого решения придется в этом направлении смотреть.
16 сен 09, 18:36    [7669695]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
Serg Alex Lad
Member

Откуда:
Сообщений: 6
хм, я хз почему, но сработала такая строка:

select (select replace(str,substring(str, patindex('%[^0-9]%', str),1),'0') as str)
from(
select '0' as str union
select '12' union
select '345' union
select '6789' union
select '1й' union
select '2я' union
select '2003гфыфвывыфв'
) a

:-/
16 сен 09, 19:05    [7669817]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
Glory
Member

Откуда:
Сообщений: 104760
Serg Alex Lad
хм, я хз почему, но сработала такая строка:


:-/

Потому что у вас все нецифры строго в конце строк
16 сен 09, 19:35    [7669911]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Serg Alex Lad
хм, я хз почему, но сработала такая строка:

:-/

У меня - не сработала.
16 сен 09, 19:36    [7669917]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
Serg Alex Lad
Member

Откуда:
Сообщений: 6
Сорри, это я ступил, БД без русской локали, все русские буквы транслировала в знаки вопроса. Отсюда и факт срабатывания.
16 сен 09, 19:52    [7669956]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
WITH Strings(S) AS
(
 select '0' as str union
 select '12' union
 select '345' union
 select '6789' union
 select '1й' union
 select '2я' union
 select '2003г'
)
,List(ID,S) AS(SELECT ROW_NUMBER()OVER(ORDER BY S), S FROM Strings)
,Symbols(ID,N,C) AS
(
 SELECT L.ID, V.number, SUBSTRING(L.S,V.number,1)
 FROM List L JOIN master.dbo.spt_values V ON V.type='P' AND V.number BETWEEN 1 AND LEN(L.S)
)
SELECT (SELECT CASE WHEN S.C NOT LIKE '[0-9]' THEN '0' ELSE S.C END FROM Symbols S WHERE S.ID=L.ID ORDER BY S.N FOR XML PATH('')) S
FROM List L
ORDER BY L.ID;
16 сен 09, 20:46    [7670086]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iap
WITH Strings(S) AS
(
 select '0' as str union
 select '12' union
 select '345' union
 select '6789' union
 select '1й' union
 select '2я' union
 select '2003г'
)
,List(ID,S) AS(SELECT ROW_NUMBER()OVER(ORDER BY S), S FROM Strings)
,Symbols(ID,N,C) AS
(
 SELECT L.ID, V.number, SUBSTRING(L.S,V.number,1)
 FROM List L JOIN master.dbo.spt_values V ON V.type='P' AND V.number BETWEEN 1 AND LEN(L.S)
)
SELECT (SELECT CASE WHEN S.C NOT LIKE '[0-9]' THEN '0' ELSE S.C END FROM Symbols S WHERE S.ID=L.ID ORDER BY S.N FOR XML PATH('')) S
FROM List L
ORDER BY L.ID;
Только вот FOR XML PATH('') плохо себя ведёт с некоторыми символами, например, '<' и '>'.
В случае необходимости строки можно собрать и, например, рекурсивным CTE
16 сен 09, 20:49    [7670090]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Прошу прощения, моё последнее глубокомысленное выступление в данном случае ни о чём.
Ведь перед сборкой все нецифры заменяются на 0.
Считайте, что моего предыдущего поста не было.
16 сен 09, 21:39    [7670236]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
Itille
Member

Откуда: Волгоград
Сообщений: 15
Если есть только первые и последние символы, а между цифр менять не надо, то так

Select str,
Case when patindex('%[^0-9]%', str) = 0 then Str
else
Case
When patindex('%[^0-9]%', str) > 1 then
Stuff(str,patindex('%[^0-9]%', str),Len(Str)-patindex('%[^0-9]%',str)+1,REPLICATE('0',Len(Str)-patindex('%[^0-9]%',str)+1) )
else
Reverse(Stuff(Reverse(str),patindex('%[^0-9]%', Reverse(str)),Len(Str)-patindex('%[^0-9]%',Reverse(str))+1,REPLICATE('0',Len(Str)-patindex('%[^0-9]%',Reverse(str))+1) ))
end
end,
patindex('%[^0-9]%', str)
from(
select '0' as str union
select '12' union
select '345' union
select 'AA6789' union
select '1й' union
select '2я' union
select '2003гфыфвывыфв'
) a
17 сен 09, 10:08    [7671221]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
Itille
Member

Откуда: Волгоград
Сообщений: 15
Без последнего столбца, конечно....

Select str,
Case when patindex('%[^0-9]%', str) = 0 then Str
else
Case
When patindex('%[^0-9]%', str) > 1 then
Stuff(str,patindex('%[^0-9]%', str),Len(Str)-patindex('%[^0-9]%',str)+1,REPLICATE('0',Len(Str)-patindex('%[^0-9]%',str)+1) )
else
Reverse(Stuff(Reverse(str),patindex('%[^0-9]%', Reverse(str)),Len(Str)-patindex('%[^0-9]%',Reverse(str))+1,REPLICATE('0',Len(Str)-patindex('%[^0-9]%',Reverse(str))+1) ))
end
end
from(
select '0' as str union
select '12' union
select '345' union
select 'AA6789' union
select '1й' union
select '2я' union
select '2003гфыфвывыфв'
) a
17 сен 09, 10:10    [7671230]     Ответить | Цитировать Сообщить модератору
 Re: Как нецифры превратить в ноль одним SELECT'ом?  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
Serg Alex Lad,

может так будет проще:)

select case when isnumeric([str])=1 THEN [str] ELSE 0 END [str]
from
(
select '0' as [str] union
select '12' union
select '345' union
select '6789' union
select '1й' union
select '2я' union
select '2003г'
) a
17 сен 09, 12:19    [7672264]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить