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

Откуда:
Сообщений: 405
Доброго дня! Подскажите можно ли как-нить в запросе извлечь только определеные номера из строки! Есть вот такие строки:

1. SC-21163: создание запроса

2. SC-11365: изменение 1
SC-18564: изменение 2
SC-18936: изменение 3

1 и 2 - это запись в таблице.

Спасибо.
15 авг 12, 13:18    [13014415]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
iiyama
Member

Откуда:
Сообщений: 642
SUBSTRING + CHARINDEX
15 авг 12, 13:30    [13014485]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
user89
Member

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

А вроде была похожая тема 12902815
По аналогии...
15 авг 12, 14:16    [13014940]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
temoxa
Member

Откуда:
Сообщений: 405
Для первого варианта, когда в строке один номер сделал так:

SET NOCOUNT ON;
DECLARE @S VARCHAR(8000);
SET @S='SC-17547: видати 5 штSC-17548: видати 5 шт';
SELECT @S;

SELECT @S=SUBSTRING(@S, CharIndex('SC-', @S, 1)+3, 5)
Select @S


а вот когда в строке два номера, что-то не получается, может кто помогёт?? спасибо.
15 авг 12, 15:56    [13015775]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
user89
Member

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

не совсем понятно...
Выбрать все номера, которые расположены между
SC-
и
:
???
15 авг 12, 16:13    [13015923]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
Glory
Member

Откуда:
Сообщений: 104751
temoxa
а вот когда в строке два номера, что-то не получается, может кто помогёт??

Дайте определение, что такое "номер", и у вас все получится
15 авг 12, 16:18    [13015954]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Печёнкой чую: нужна таблица с числами!
15 авг 12, 16:20    [13015967]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
iiyama
Member

Откуда:
Сообщений: 642
CHARINDEX ( expression1 ,expression2 [ , start_location ] ) 
- start_location

PS.если возникнет вопрос, а что делать если есть 3-й номер, то прислушайтесь к совету Glory
15 авг 12, 16:24    [13015988]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
temoxa
Member

Откуда:
Сообщений: 405
Glory,
даные у меня в таблице так как на рисунке. я хочу извлечь максимальный номер в данном случае 7421!

К сообщению приложен файл. Размер - 18Kb
15 авг 12, 16:31    [13016051]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
Glory
Member

Откуда:
Сообщений: 104751
temoxa
я хочу извлечь максимальный номер в данном случае 7421!

Дайте определение понятия "номер".
А не повторяйте много раз, чего вы хотите.
15 авг 12, 16:39    [13016111]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
temoxa
Member

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

Номер - это значения SC-11111 в строке.
15 авг 12, 16:56    [13016265]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
Glory
Member

Откуда:
Сообщений: 104751
temoxa
Glory,

Номер - это значения SC-11111 в строке.

Тогда все просто

SELECT @S=SUBSTRING(@S, CharIndex('SC-11111', @S, 1), 8)
15 авг 12, 17:13    [13016443]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
temoxa
Member

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

не все так просто! значение после SC-______ разное...
15 авг 12, 17:25    [13016544]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
Glory
Member

Откуда:
Сообщений: 104751
temoxa
значение после SC-______ разное...

Замечательно
Т.е. SC-HF1634gg123 тоже номрер ?
А SC-HF1634gg123:;? HA173 ?
15 авг 12, 17:29    [13016565]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
temoxa
Member

Откуда:
Сообщений: 405
Glory,
Структура этого номера строгая - это SC-12345...
после дифиса толька цифры, которые мне и надо!
Просто вопрос в другом, если номер в строке один, я его извлекаю, а если таких номеров два - не получается!
Вот пример строки SC-12345 SC-12346. Как из этой строки извлечь 12345 12346, а из этих значений максимум, те 12346??
15 авг 12, 17:49    [13016709]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
Glory
Member

Откуда:
Сообщений: 104751
temoxa
после дифиса толька цифры, которые мне и надо!

Замечательно
Стало быть после SC- вам всего лишь надо найти первую НЕцифру и вы получите ваш номер.
15 авг 12, 18:12    [13016917]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
temoxa,

Если в номере SC-11365 будет цифр <>5, решение ниже работать не будет
DECLARE @tab table (nk int, dd varchar(max))
INSERT INTO @tab 
SELECT 1, 'DDFSC-17547: видати 5 штSC-17548: видати 5 шт';

SELECT 
REVERSE(
SUBSTRING(
    REVERSE(DD), CharIndex('-CS', REVERSE(DD), 1)-5, 5)  
    )
FROM @tab
15 авг 12, 18:28    [13017019]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
komrad
Member

Откуда:
Сообщений: 5739
trew
temoxa,

Если в номере SC-11365 будет цифр <>5, решение ниже работать не будет
DECLARE @tab table (nk int, dd varchar(max))
INSERT INTO @tab 
SELECT 1, 'DDFSC-17547: видати 5 штSC-17548: видати 5 шт';

SELECT 
REVERSE(
SUBSTRING(
    REVERSE(DD), CharIndex('-CS', REVERSE(DD), 1)-5, 5)  
    )
FROM @tab


автору нужен максимальный номер

с такой строкой не сработает
'DDFSC-17566: видати 5 штSC-17548: видати 5 шт:SC-17555:'
15 авг 12, 18:33    [13017052]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
mike909
Member

Откуда:
Сообщений: 662
temoxa
Glory,
Структура этого номера строгая - это SC-12345...
после дифиса толька цифры, которые мне и надо!
Просто вопрос в другом, если номер в строке один, я его извлекаю, а если таких номеров два - не получается!
Вот пример строки SC-12345 SC-12346. Как из этой строки извлечь 12345 12346, а из этих значений максимум, те 12346??

Так пойдет ?
DECLARE @S VARCHAR(8000)
SET @S='SC-17547: видати 5 штSC-17548: видати 5 штSC-9760'

;with idx as (
  SELECT substring( @S, i + 3, Len(@S)) as [s]
  FROM ( SELECT charindex('SC-', @S ) as [i] ) as i
  WHERE i.i != 0

  union all

  select substring( s.s, i + 3, Len(s.s)) as [s]
  from idx as s
  CROSS APPLY (SELECT charindex('SC-', s.s ) as [i]) as i
  WHERE i.i != 0
)
SELECT top 1 *
FROM (
  select cast(SUBSTRING(t.s, 1, CASE i.i WHEN 0 THEN Len(t.s) else i.i -1 end) as bigint) as [Res]
  from idx as t
  CROSS APPLY(
    SELECT PATINDEX('%[^0-9]%', t.s) as i
  ) as i
) as t
ORDER BY t.Res DESC
15 авг 12, 18:39    [13017070]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
user89
Member

Откуда:
Сообщений: 2083
temoxa
Структура этого номера строгая - это SC-12345...
после дифиса толька цифры, которые мне и надо!

Предположим, в таблице всё-таки есть "не те" записи
s
SC-17547: видати 5 штSC-17548: видати 5 штsc-1234
sc-12
1234
23qw
zz

Тогда можно так:
set nocount on;
declare @t table (s varchar(8000))
insert @t select 'SC-17547: видати 5 штSC-17548: видати 5 штsc-1234' union all select 'sc-12' union all select '1234' union all select '23qw' union all select 'zz'

;with t1 as (select s, s + 'sc-' [a] from @t)
,t2 as
(
  select s, substring(a,t+3,8000) [a], left(a,t-1) [st],
  left(left(a,t-1), isnull(nullif(charindex('sc-',s)  * patindex('%[^0-9]%', left(a,t-1)) - 1, -1), 8000)) [nomer]
  from t1 outer apply(select charindex('sc-',a)t)t
  union all
  select s, substring(a,t+3,8000), left(a,t-1) [st],
  left(left(a,t-1), isnull(nullif(charindex('sc-',s)  * patindex('%[^0-9]%', left(a,t-1)) - 1, -1), 8000)) [nomer]
  from t2 cross apply(select charindex('sc-',a)t)t
  where t > 0
)
select top 1 with ties
s, nomer
from t2
where st <> ''
order by row_number() over(partition by s order by s, nomer desc)
15 авг 12, 22:00    [13017707]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
Без рекурсии:
set nocount on;

declare @t table (id int, s varchar(8000));

insert @t
 select 1, 'SC-17547: видати 5 штSC-17548: видати 5 штsc-1234' union all
 select 2, 'sc-12' union all
 select 3, '1234' union all
 select 4, '23qw' union all
 select 5, 'zz' union all
 select 6, 'fffSC-ggg';

with ten_digits as
(
 select
  d
 from
  (values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) t(d)
),
n as
(
 select
  row_number() over (order by (select 1)) as n
 from
  ten_digits t1 cross join
  ten_digits t2
),
x as
(
 select
  id, substring(t.s, n.n + 3, len(s) - n.n - 3 + 1) as v
 from
  @t t join
  n on n.n <= len(t.s) - 3
 where
  substring(t.s, n.n, len(s) + 1) like 'SC-[0-9]%'
)
select
 id, max(cast(substring(v, 1, isnull(nullif(patindex('%[^0-9]%', v), 0) - 1, len(v))) as int))
from
 x
group by
 id;
15 авг 12, 23:54    [13018042]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
aleks2
Guest
Проще нада быть

create function [dbo].[f_StrToTableEx](@str varchar(8000), @delimiter varchar(64)=',')
returns table 
as 
return(
WITH str_nums ( n1, n2, Number ) AS ( 
       select  1-LEN(@delimiter) as n1
             , charindex(@delimiter, @str+@delimiter) as n2, 0 as Number  
       UNION ALL 
       select n2 as n1, charindex(@delimiter, @str+@delimiter, n2+LEN(@delimiter)) as n2, Number+1 as Number
         from str_nums 
       WHERE n2<len(@str)
     ) 
SELECT SUBSTRING(@str, n1+LEN(@delimiter), n2-n1-LEN(@delimiter)) as Value, Number, n1+LEN(@delimiter) as StartPosition FROM str_nums) 



DECLARE @S VARCHAR(8000)
SET @S='SC-17547: видати 5 штSC-17548: видати 5 штSC-9760'

select SUBSTRING(Value, 1, isnull( nullif(PATINDEX('%[^0-9]%', Value), 0) - 1, 99) ) as N from [dbo].[f_StrToTableEx](@S, 'SC-') where Value like '[0-9]%'
16 авг 12, 05:52    [13018371]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
мимо
Guest
declare @t table (id int, s varchar(8000));

insert @t
 select 1, 'SC-17547: видати 5 штSC-17548: видати 5 штsc-1234' union all
 select 2, 'sc-12' union all
 select 3, '1234' union all
 select 4, '23qw' union all
 select 5, 'zz' union all
 select 6, 'fffSC-ggg' union all
 select 7, 'SC-17547: видати 5 штSC-17548: видати 5 штsc-y1234 SC-17549' 

select id
,max(cast(SUBSTRING(va,pB,LEN(va) - pB-Pf +2) as int))
from (
	select z.*
	, q.w.value('.','varchar(50)') as va
	,PATINDEX('%[0-9]%', q.w.value('.','varchar(50)')) as pB
	,PATINDEX('%[0-9]%', reverse(q.w.value('.','varchar(50)'))) as Pf
	,CHARINDEX('SC-',q.w.value('.','varchar(50)')) as ci
	from (select t.id 
				,cast(('<b>' + replace(t.s,' ','</b><b>') + '</b>') as xml) as y
			from @t as t
			) as z 
			cross apply z.y.nodes('b') as q(w) 
	) as z
where ci> 0
	and PATINDEX('%[0-9]%',SUBSTRING(va,pB,LEN(va) - pB-Pf +2))> 0 
group by id	
16 авг 12, 10:08    [13018809]     Ответить | Цитировать Сообщить модератору
 Re: извлечние номера из строки  [new]
temoxa
Member

Откуда:
Сообщений: 405
Всем спасибо за помощь.
16 авг 12, 19:54    [13022930]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить