Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 выборка чисел из строки  [new]
Андрей Макаревич
Member

Откуда:
Сообщений: 13
UPDATE t2
SET t2.phone1 = ???
--replace(t1.phone, [^09], '')
FROM t1
inner join t2
on t1.nk = t2.nk


в таблице Т1 содержаться телефоны в формате (123)45-67-890
как сделать так чтобы в таблицу Т2 записывались "чистые" телефоны без всяких знаков?
было решение сделать несколько вложенных replace'ов чтобы удалить скобки и тире,
а как реализовать выборку в том случае, если мы не знаем какой символ там прописан?
т.е. в базе такой номер: (123А45-67В890, а выбрать нужно только цифры
27 июл 09, 21:58    [7466470]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
DECLARE @C CHAR;
SET @C='';
WHILE @@ROWCOUNT>0
 UPDATE t2
 SET phone1=REPLACE(phone1, SUBSTRING(phone1, PATINDEX('%[^0-9]%',phone1),1),'')
 WHERE phone1 LIKE '%[^0-9]%';
28 июл 09, 09:48    [7467109]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Может быть, версию сервера назовёте?
28 июл 09, 09:49    [7467114]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
iljy
Member

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

у вас получается многократное сканирование таблицы, даже для самого простого варианта "(123) 456-78-90" - 4 прохода! ИМХО на приличной таблице это будет дороговато По-моему автору есть прямой смысл написать UDF, которая сдалет удаление лишних символов из строки за один раз. Здесь цена за вызов UDF наверняка окажется меньше, чем за многократное сканирование. Хотя бы так:
CREATE FUNCTION ProcessPhoneNumber
(
	-- Add the parameters for the function here
	@S varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
	declare @i int
	set @i = PATINDEX('%[^0-9]%',@s)
	while @i != 0
	begin
		set @s = REPLACE(@s, SUBSTRING(@s, @i, 1),'')
		set @i = PATINDEX('%[^0-9]%',@s)
	end

	RETURN @s
END
28 июл 09, 10:23    [7467321]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iljy
iap,

у вас получается многократное сканирование таблицы, даже для самого простого варианта "(123) 456-78-90" - 4 прохода! ИМХО на приличной таблице это будет дороговато По-моему автору есть прямой смысл написать UDF, которая сдалет удаление лишних символов из строки за один раз. Здесь цена за вызов UDF наверняка окажется меньше, чем за многократное сканирование. Хотя бы так:
CREATE FUNCTION ProcessPhoneNumber
(
	-- Add the parameters for the function here
	@S varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
	declare @i int
	set @i = PATINDEX('%[^0-9]%',@s)
	while @i != 0
	begin
		set @s = REPLACE(@s, SUBSTRING(@s, @i, 1),'')
		set @i = PATINDEX('%[^0-9]%',@s)
	end

	RETURN @s
END
Слова, одни слова!
Но ждём-с номер версии однако!

P.S. не уверен, что скалярная функция, вызываемая для каждой строки, эффективнее.
Проверять просто нет времени.
28 июл 09, 10:47    [7467465]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
iljy
Member

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

честно говоря проверять тоже лень, но интуитивно мне кажется, что многократное сканирование таблицы, да еще и с изменениями - может встать сильно дороже. Но тут непонятно - какого размера таблица, сколько индексов, насколько замусорены номера и т.п.
28 июл 09, 11:13    [7467664]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
OK96
Member

Откуда: Москва
Сообщений: 56
Конвертирует всякий бред в номерах телефонов в стандарт "79261234567" или "+74951234567".
Формат зависит от @intGetType:
CREATE FUNCTION dbo.fnSysGetConvertPhoneNumber (@nvcPhoneNumber nvarchar(30), @intGetType int = 0)
RETURNS nvarchar(200)
AS
BEGIN
	DECLARE @ncvReturn as nvarchar(30)
	DECLARE @nvcCityPref nvarchar(5)
	SET @nvcCityPref = '495'
	IF ISNULL(@nvcPhoneNumber,'')<>''
	BEGIN
		SET @ncvReturn = 
	
		REPLACE(REPLACE(REPLACE(@nvcPhoneNumber,'+', ''),'-', ''),' ', '')
		SET @ncvReturn = LTRIM(RTRIM(@ncvReturn))
		IF LEN(@ncvReturn)=7
			SET @ncvReturn = '7' + @nvcCityPref + @ncvReturn
		IF LEN(@ncvReturn)=10 AND LEFT(@ncvReturn,1)='9'
			SET @ncvReturn = '7' + @ncvReturn
		ELSE IF LEN(@ncvReturn)=11 AND LEFT(@ncvReturn,1)='8'
			SET @ncvReturn = '7' + SUBSTRING(@ncvReturn,2,LEN(@ncvReturn)-1)
		SET @ncvReturn = LEFT(@ncvReturn,11)
		IF ISNULL(@intGetType,0)=1
			SET @ncvReturn = '+' + @ncvReturn
	END
	ELSE
		SET @ncvReturn = ''

	RETURN @ncvReturn
END


GO
28 июл 09, 11:59    [7468040]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
Андрей Макаревич
Member

Откуда:
Сообщений: 13
iap
Может быть, версию сервера назовёте?

двухтышно пятый
28 июл 09, 12:56    [7468386]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Андрей Макаревич
iap
Может быть, версию сервера назовёте?

двухтышно пятый
SET NOCOUNT ON;
USE tempdb;

IF OBJECT_ID(N't1','U') IS NOT NULL DROP TABLE t1;
CREATE TABLE t1(nk INT,phone VARCHAR(100));
GO

INSERT t1(nk,phone) SELECT 1,'123А45-67В890' UNION ALL SELECT 2, '764576-+GGG9887';
GO

SELECT
 t1.nk
,(
  SELECT ''+SUBSTRING(t.phone,v.number,1)
  FROM t1 t JOIN master.dbo.spt_values v
  ON v.type='P' AND v.number BETWEEN 1 AND LEN(t.phone) AND SUBSTRING(t.phone,v.number,1)LIKE'[0-9]'
  WHERE t.nk=t1.nk
  ORDER BY v.number
  FOR XML PATH('')
 ) phone
FROM t1
GROUP BY t1.nk;
28 июл 09, 13:26    [7468542]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iap
Андрей Макаревич
iap
Может быть, версию сервера назовёте?

двухтышно пятый
SET NOCOUNT ON;
USE tempdb;

IF OBJECT_ID(N't1','U') IS NOT NULL DROP TABLE t1;
CREATE TABLE t1(nk INT,phone VARCHAR(100));
GO

INSERT t1(nk,phone) SELECT 1,'123А45-67В890' UNION ALL SELECT 2, '764576-+GGG9887';
GO

SELECT
 t1.nk
,(
  SELECT ''+SUBSTRING(t.phone,v.number,1)
  FROM t1 t JOIN master.dbo.spt_values v
  ON v.type='P' AND v.number BETWEEN 1 AND LEN(t.phone) AND SUBSTRING(t.phone,v.number,1)LIKE'[0-9]'
  WHERE t.nk=t1.nk
  ORDER BY v.number
  FOR XML PATH('')
 ) phone
FROM t1
GROUP BY t1.nk;
Кое-что лишнее оказалось
SET NOCOUNT ON;
USE tempdb;

IF OBJECT_ID(N't1','U') IS NOT NULL DROP TABLE t1;
CREATE TABLE t1(nk INT,phone VARCHAR(100));
GO

INSERT t1(nk,phone) SELECT 1,'123А45-67В890' UNION ALL SELECT 2, '764576-+GGG9887';
GO

SELECT
 t1.nk
,(
  SELECT SUBSTRING(t.phone,v.number,1)
  FROM t1 t JOIN master.dbo.spt_values v
  ON v.type='P' AND v.number BETWEEN 1 AND LEN(t.phone) AND SUBSTRING(t.phone,v.number,1)LIKE'[0-9]'
  WHERE t.nk=t1.nk
  ORDER BY v.number
  FOR XML PATH('')
 ) phone
FROM t1
GROUP BY t1.nk;
28 июл 09, 13:28    [7468550]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
Гость форума
Guest
iap,
+5
Правда тот же результат можно получить без [GROUP BY t1.nk]
declare @t1 table (nk int,phone varchar(100))

insert @t1(nk,phone) select 1,'вапа123А45-67В890вап' union ALL select 2, 'вапа64576-+GGG9887sfgsd'
select * from @t1

select t1.nk,
(
  select substring(t.phone,v.number,1)
  from @t1 t join master.dbo.spt_values v
  on v.type = 'P' and v.number between 1 and len(t.phone) and substring(t.phone,v.number,1) like '[0-9]'
  where t.nk = t1.nk
  for xml path('')
 ) phone
from @t1 t1
28 июл 09, 14:37    [7469024]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Гость форума,

действительно! Заработался...
28 июл 09, 15:32    [7469519]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iap
Гость форума,

действительно! Заработался...
Только вот ORDER BY куда делся?
IMHO, он нужен был для гарантированно правильной сборки строки...
28 июл 09, 15:53    [7469680]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
Гость форума
Guest
iap
iap
Гость форума,

действительно! Заработался...
Только вот ORDER BY куда делся?
IMHO, он нужен был для гарантированно правильной сборки строки...

Сначала я оставил Order by, потом убрал, результат получился такой же...
28 июл 09, 15:59    [7469734]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
iljy
Member

Откуда:
Сообщений: 8711
Гость форума,

он скорее всего будет такой же, потому что сканирование пойдет по индексу. Но все-таки его лучше оставить, в другой ситуации все может оказаться не столь радужно
28 июл 09, 16:01    [7469757]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Гость форума
iap
iap
Гость форума,

действительно! Заработался...
Только вот ORDER BY куда делся?
IMHO, он нужен был для гарантированно правильной сборки строки...

Сначала я оставил Order by, потом убрал, результат получился такой же...
Но это только в этот раз.
Гарантию даёт только ORDER BY
28 июл 09, 16:02    [7469763]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
Гость форума
Guest
iljy,
ОК.
28 июл 09, 16:02    [7469766]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
Андрей Макаревич
Member

Откуда:
Сообщений: 13
я только начал учить sql, потому сильно не пинать =)
скажите, что такое и зачем нужно:
master.dbo.spt_values
 FOR XML PATH('')
28 июл 09, 20:38    [7471094]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
Андрей Макаревич
Member

Откуда:
Сообщений: 13
Забыл сказать, ВСЕМ БОЛЬШОЕ СПАСИБО!!! =)
28 июл 09, 20:41    [7471099]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
Андрей Макаревич
Member

Откуда:
Сообщений: 13
а как будет выглядеть запрос если
есть таблица: и другая таблица:
---------+-------------------         +-------------
phone_id | phone                      | clean_phone
---------+-------------------         +-------------
     001 | 8(123)456-78-90            |
     004 | 8(917)234-75-74            |
     012 | 8Щ3567)-2-34-64-4          |
     005 | 8(46733)23-23-6            |

нужно , чтобы выбирался один телефон -> очистка -> занесение во вторую таблицу, потом следующий номер и так до конца.
сказали, через while можно корявенько сделать. не могу допетрить как?
28 июл 09, 21:11    [7471151]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Андрей Макаревич
а как будет выглядеть запрос если
есть таблица: и другая таблица:
---------+-------------------         +-------------
phone_id | phone                      | clean_phone
---------+-------------------         +-------------
     001 | 8(123)456-78-90            |
     004 | 8(917)234-75-74            |
     012 | 8Щ3567)-2-34-64-4          |
     005 | 8(46733)23-23-6            |

нужно , чтобы выбирался один телефон -> очистка -> занесение во вторую таблицу, потом следующий номер и так до конца.
сказали, через while можно корявенько сделать. не могу допетрить как?
А что,
INSERT Table1(...) SELECT ... FROM ...
или
UPDATE Table1 SET ... FROM ...
уже отменили?
А я думал, что написал Вам и SELECT для вышеозначенных INSERT или UPDATE и даже WHILE для "очистки" (но не советую).
Что касается spt_values, то это системная таблица, которая здесь просто демонстрирует использование таблицы
с последовательными целыми числами. Кстати, их там запросто может оказаться недостаточно.
Хорошо иметь свою таблицу с числами.

Извините, а Вы знаете, что такое JOIN?
28 июл 09, 22:17    [7471270]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
iljy
Member

Откуда:
Сообщений: 8711
Андрей Макаревич

master.dbo.spt_values

системная таблица. в ней помимо прочей инфы содержатся последовательные числа от 0 до 2048, поэтому ее удобно использовать для генерации последовательности. Но в общем случае метод не подходящий, потому что у пользователя может не быть доступа к системной базе. Если надо делать это для пользовательских запросов - можно создать свою таблицу-нумератор либо использовать генерацию с помощью CTE.

автор

 FOR XML PATH('')

преобразование выборки в формат XML. В данном случае задаются пустые имена тегов. Посмотрите например
SELECT ''+x
  FROM 
	(select '1' x union all select '2' union all select '3') t
  FOR XML PATH('')
28 июл 09, 22:28    [7471313]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
Андрей Макаревич
Member

Откуда:
Сообщений: 13
iap

Извините, а Вы знаете, что такое JOIN?

=) да, спасибо, с JOIN'ами я вроде как разобрался.
в моем учебнике нет ни слова про spt_values

iap да и все остальные,
ребят,если не сложно,
посоветуйте хорошую книгу для начинающего?
29 июл 09, 00:14    [7471512]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4255
OK96
Конвертирует всякий бред в номерах телефонов в стандарт "79261234567" или "+74951234567".
Формат зависит от @intGetType:
CREATE FUNCTION dbo.fnSysGetConvertPhoneNumber (@nvcPhoneNumber nvarchar(30), 
GO


а американский стандарт с точками не поддерживает. К томуже не удаляет скобки
29 июл 09, 05:07    [7471646]     Ответить | Цитировать Сообщить модератору
 Re: выборка чисел из строки  [new]
Андрей Макаревич
Member

Откуда:
Сообщений: 13
а на Server 2008 substring varchar не поддерживает что-ли? выдает мне след ошибку
Сообщение 8116, уровень 16, состояние 1, строка 4
Тип данных аргумента varchar недопустим для аргумента 2 функции substring
29 июл 09, 12:53    [7473335]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить