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

Откуда:
Сообщений: 545
Есть столбец со следующим содержимым:
field100=Иванов&field101=Иван&field102=4000.00
field100=Петров&field101=Петр&field102=5000.00
...

Возможно ли получить из них вот такое???
Иванов Иван
Петров Петр
...

Спасибо.
14 май 12, 10:45    [12548367]     Ответить | Цитировать Сообщить модератору
 Re: Как удалить лишнее из строки?  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
2king2, да, SUBSTRING + CHARINDEX вам в помощь.
14 май 12, 10:57    [12548453]     Ответить | Цитировать Сообщить модератору
 Re: Как удалить лишнее из строки?  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
DECLARE @Delimiters VARCHAR(100)='=&';
WITH
 T(ID,F)AS
 (
  SELECT 1,'field100=Иванов&field101=Иван&field102=4000.00'
  UNION ALL
  SELECT 2,'field100=Петров&field101=Петр&field102=5000.00'
 )
 SELECT ID,
  STUFF
  (
   (
    SELECT ' '+F
    FROM
    (
     SELECT TT.ID,ROW_NUMBER()OVER(PARTITION BY TT.ID ORDER BY V.number),SUBSTRING(TT.F COLLATE Cyrillic_General_CI_AI,V.number,
      (
       SELECT MIN(VV.number)
       FROM master.dbo.spt_values VV
       WHERE VV.type='P' AND VV.number BETWEEN V.number AND LEN(TT.F)+1
       AND CHARINDEX(SUBSTRING(TT.F+LEFT(@Delimiters,1),VV.number,1),@Delimiters)>0
      )-V.number
     )
     FROM master.dbo.spt_values V
     JOIN T TT ON V.type='P' AND V.number BETWEEN 1 AND LEN(TT.F)+1
     AND CHARINDEX(SUBSTRING(LEFT(@Delimiters,1)+TT.F,V.number,1),@Delimiters)>0
     WHERE TT.ID=T.ID
    )T(ID,N,F)
    WHERE N IN(2,4)
    ORDER BY ID,N
    FOR XML PATH(''),TYPE
   ).value('text()[1]','varchar(max)'),1,1,''
  ) F
 FROM T
 ORDER BY ID;
14 май 12, 11:44    [12548802]     Ответить | Цитировать Сообщить модератору
 Re: Как удалить лишнее из строки?  [new]
2king2
Member

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

iap,

Огооо, спасибо!!! Сейчас вместо кириллицы вопросы. Где что надо настроить подскажите?

К сообщению приложен файл. Размер - 3Kb
14 май 12, 11:57    [12548909]     Ответить | Цитировать Сообщить модератору
 Re: Как удалить лишнее из строки?  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
2king2,

DECLARE @Test1 VARCHAR(10), @Test2 NVARCHAR(10)
SELECT @Test1='Тест', @Test2=N'Тест'
SELECT @Test1, @Test2
14 май 12, 12:01    [12548939]     Ответить | Цитировать Сообщить модератору
 Re: Как удалить лишнее из строки?  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Ну и еще как вариация на тему:
;WITH
T(ID,F)AS
(
	SELECT 1,N'field100=Иванов&field101=Иван&field102=4000.00'
	UNION ALL
	SELECT 2,N'field100=Петров&field101=Петр&field102=5000.00'
)
SELECT id, (
SELECT --t.id
       --,t1.rn
       t1.v [data()]
FROM    ( SELECT    id
                   ,CAST(N'<r><f>' + REPLACE(REPLACE(F, N'=', N'</f><c>'), N'&', N'</c><f>') + N'</c></r>' AS XML) s
          FROM      T
        ) t
        CROSS APPLY ( SELECT    x.z.value('.', 'nvarchar(max)') v
                               ,ROW_NUMBER() OVER ( PARTITION BY id ORDER BY ( SELECT   1
                                                                             ) ) rn
                      FROM      s.nodes(N'/r/c') x ( z )
                    ) t1
WHERE  t2.id=t.id AND t1.rn<3
FOR XML PATH (N'')
) res
FROM T t2
14 май 12, 12:24    [12549088]     Ответить | Цитировать Сообщить модератору
 Re: Как удалить лишнее из строки?  [new]
2king2
Member

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

Спасибо огромное!!! Работает!

Не сочтите за наглость, а как сделать так чтобы каждое новое слово начиналось с заглавной буквы?
иванов иван
петров петр

на
Иванов Иван
Петров Петр
14 май 12, 12:37    [12549198]     Ответить | Цитировать Сообщить модератору
 Re: Как удалить лишнее из строки?  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
2king2,

;WITH
T(ID,F)AS
(
	SELECT 1,N'field100=иванов&field101=иван&field102=4000.00'
	UNION ALL
	SELECT 2,N'field100=петров&field101=петр&field102=5000.00'
	UNION ALL
	SELECT 3,N'field100=простофедя&field101=&field102=666.00'
	UNION ALL
	SELECT 4,N'field100=&field101=&field102=666.00'
)
SELECT id, (
SELECT --t.id
       --,t1.rn
       t1.v [data()]
FROM    ( SELECT    id
                   ,CAST(N'<r><f>' + REPLACE(REPLACE(F, N'=', N'</f><c>'), N'&', N'</c><f>') + N'</c></r>' AS XML) s
          FROM      T
        ) t
        CROSS APPLY ( SELECT    UPPER(LEFT(x.z.value('.', 'nvarchar(max)'),1))+RIGHT(x.z.value('.', 'nvarchar(max)'),NULLIF(LEN(x.z.value('.', 'nvarchar(max)')),0)-1) v
                               ,ROW_NUMBER() OVER ( PARTITION BY id ORDER BY ( SELECT   1
                                                                             ) ) rn
                      FROM      s.nodes(N'/r/c') x ( z )
                    ) t1
WHERE  t2.id=t.id AND t1.rn<3
FOR XML PATH (N'')
) res
FROM T t2
14 май 12, 12:45    [12549252]     Ответить | Цитировать Сообщить модератору
 Re: Как удалить лишнее из строки?  [new]
2king2
Member

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

Спасибо, работает. Вот решил на выходных поковыряться еще. А есть возможность разделить результат запроса, в Вашем примере, на отделенные столбцы?
Столбец1  | Столбец2
----------+---------
Иванов    | Иван
Петров    | Петр
19 май 12, 18:40    [12582231]     Ответить | Цитировать Сообщить модератору
 Re: Как удалить лишнее из строки?  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
2king2
А есть возможность разделить результат запроса, в Вашем примере, на отделенные столбцы?

Да, естественно. PIVOT вместо FOR XML
19 май 12, 19:09    [12582301]     Ответить | Цитировать Сообщить модератору
 Re: Как удалить лишнее из строки?  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
2king2
kDnZP,

Спасибо, работает. Вот решил на выходных поковыряться еще. А есть возможность разделить результат запроса, в Вашем примере, на отделенные столбцы?
Столбец1  | Столбец2
----------+---------
Иванов    | Иван
Петров    | Петр
DECLARE @Delimiters VARCHAR(100)='=&';
WITH
 CTE(ID,F)AS
 (
  SELECT 1,'field100=иванов&field101=иван&field102=4000.00'
  UNION ALL
  SELECT 2,'field100=петров&field101=петр&field102=5000.00'
 )
SELECT T.ID
,[Столбец1]=MAX(CASE T.N WHEN 2 THEN STUFF(T.F,1,1,UPPER(LEFT(T.F,1))) ELSE '' END)
,[Столбец2]=MAX(CASE T.N WHEN 4 THEN STUFF(T.F,1,1,UPPER(LEFT(T.F,1))) ELSE '' END)
FROM
(
 SELECT CTE.ID,ROW_NUMBER()OVER(PARTITION BY CTE.ID ORDER BY V.number),SUBSTRING(CTE.F COLLATE Cyrillic_General_CI_AI,V.number,
 (
  SELECT MIN(VV.number)
  FROM master.dbo.spt_values VV
  WHERE VV.type='P' AND VV.number BETWEEN V.number AND LEN(CTE.F)+1
    AND CHARINDEX(SUBSTRING(CTE.F+LEFT(@Delimiters,1),VV.number,1),@Delimiters)>0
  )-V.number
 )
 FROM master.dbo.spt_values V
 JOIN CTE ON V.type='P' AND V.number BETWEEN 1 AND LEN(CTE.F)+1
  AND CHARINDEX(SUBSTRING(LEFT(@Delimiters,1)+CTE.F,V.number,1),@Delimiters)>0
)T(ID,N,F)
WHERE N IN(2,4)
GROUP BY ID
ORDER BY ID;
19 май 12, 19:11    [12582308]     Ответить | Цитировать Сообщить модератору
 Re: Как удалить лишнее из строки?  [new]
2king2
Member

Откуда:
Сообщений: 545
kDnZP
Да, естественно. PIVOT вместо FOR XML

Замена FOR XML на PIVOT дает ошибку о некорректности синтаксиса использования PIVOT
WHERE  t2.id=t.id AND t1.rn<3
PIVOT PATH (N'')  --FOR XML PATH (N'') 
) res
FROM T t2

Эх, пошел гуглить
19 май 12, 19:57    [12582425]     Ответить | Цитировать Сообщить модератору
 Re: Как удалить лишнее из строки?  [new]
2king2
Member

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

С формированием пивот-таблицы не получилось разобраться

iap,

Спасибо, в приципе ваш вариант вполне работает, но я стал его стал адаптировать под себя и у меня не получается вывести все столбыцы которые я указал в запросе. Выводяться только [Рас. счет],[Фамилия],[Имя],[Отчество]. Как мне быть?
DECLARE @Delimiters VARCHAR(100)='=&';
--WITH
-- CTE(ID,F)AS
-- (
--  SELECT 1,'field100=иванов&field101=иван&field102=4000.00'
--  UNION ALL
--  SELECT 2,'field100=петров&field101=петр&field102=5000.00'
-- )
WITH CTE(ID, F, C1, C2, C3, C4, C5, C6, C7, C8) AS 
(SELECT [ProcessDB].dbo.PreprocessingPayments.[Id], 
[ProcessDB].dbo.PreprocessingPayments.[Params], 
[ProcessDB].dbo.PreprocessingPayments.TerminalID, [ProcessDB].dbo.Terminals.Name, 
[ProcessDB].dbo.Operators.Name, CONVERT(varchar, 
[ProcessDB].dbo.PreprocessingPayments.TerminalDateTime, 104), CONVERT(varchar, 
[ProcessDB].dbo.PreprocessingPayments.ServerDateTime, 104), CONVERT(money, 
[ProcessDB].dbo.PreprocessingPayments.AmountAll), CONVERT(money, 
[ProcessDB].dbo.PreprocessingPayments.Amount), CONVERT(money, 100)
FROM[ProcessDB].dbo.Terminals INNER JOIN
[ProcessDB].dbo.PreprocessingPayments ON 
[ProcessDB].dbo.Terminals.TerminalID = [ProcessDB].dbo.PreprocessingPayments.TerminalID
INNER JOIN
[ProcessDB].dbo.CyberplatOperators INNER JOIN
[ProcessDB].dbo.Operators ON 
[ProcessDB].dbo.CyberplatOperators.OperatorID = [ProcessDB].dbo.Operators.OperatorID ON 
[ProcessDB].dbo.PreprocessingPayments.ProviderId = [ProcessDB].dbo.CyberplatOperators.OperatorID
INNER JOIN
[ProcessDB].dbo.Gateways ON 
[ProcessDB].dbo.PreprocessingPayments.ProcessingType = [ProcessDB].dbo.Gateways.Id AND
[ProcessDB].dbo.CyberplatOperators.ProcessingType = [ProcessDB].dbo.Gateways.Id
WHERE [PreprocessingPayments].ProcessingType = 11 
--AND DATEDIFF(DAY, [ProcessDB].dbo.PreprocessingPayments.InitializeDateTime, GETDATE()) = 1)
AND [ProcessDB].dbo.PreprocessingPayments.InitializeDateTime > '2012-04-28'
AND [ProcessDB].dbo.PreprocessingPayments.TerminalID = 1758)

 
SELECT T.ID
,[Рас. счет]=MAX(CASE T.N WHEN 2 THEN STUFF(T.F,1,1,UPPER(LEFT(T.F,1))) ELSE '' END)
,[Фамилия]  =MAX(CASE T.N WHEN 4 THEN STUFF(T.F,1,1,UPPER(LEFT(T.F,1))) ELSE '' END)
,[Имя]      =MAX(CASE T.N WHEN 6 THEN STUFF(T.F,1,1,UPPER(LEFT(T.F,1))) ELSE '' END)
,[Отчество] =MAX(CASE T.N WHEN 8 THEN STUFF(T.F,1,1,UPPER(LEFT(T.F,1))) ELSE '' END)
FROM
(
 SELECT CTE.ID,ROW_NUMBER()OVER(PARTITION BY CTE.ID ORDER BY V.number),SUBSTRING(CTE.F COLLATE Cyrillic_General_CI_AI,V.number,
 (
  SELECT MIN(VV.number)
  FROM master.dbo.spt_values VV
  WHERE VV.type='P' AND VV.number BETWEEN V.number AND LEN(CTE.F)+1
    AND CHARINDEX(SUBSTRING(CTE.F+LEFT(@Delimiters,1),VV.number,1),@Delimiters)>0
  )-V.number
 )
 FROM master.dbo.spt_values V
 JOIN CTE ON V.type='P' AND V.number BETWEEN 1 AND LEN(CTE.F)+1
  AND CHARINDEX(SUBSTRING(LEFT(@Delimiters,1)+CTE.F,V.number,1),@Delimiters)>0
)T(ID,N,F)
WHERE N IN(2,4,6,8)
GROUP BY T.ID
ORDER BY ID;
19 май 12, 21:49    [12582770]     Ответить | Цитировать Сообщить модератору
 Re: Как удалить лишнее из строки?  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
2king2,

что же можно сделать, если таблица [ProcessDB].dbo.PreprocessingPayments и все прочие есть только у Вас?!

Да и формат строк здесь, строго говоря, не формализован - я писал, опираясь только на интуицию.
Глядя на приведённые в первом посте примеры.
Но все ли строки устроены так, а не иначе?
19 май 12, 21:58    [12582812]     Ответить | Цитировать Сообщить модератору
 Re: Как удалить лишнее из строки?  [new]
2king2
Member

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

что же можно сделать, если таблица [ProcessDB].dbo.PreprocessingPayments и все прочие есть только у Вас?!

Да и формат строк здесь, строго говоря, не формализован - я писал, опираясь только на интуицию.
Глядя на приведённые в первом посте примеры.
Но все ли строки устроены так, а не иначе?


Не, не... остальные строки нормальные и типизированные, это обычные INT. Их нужно вывести так как они записаны в базе, без постобработки.
19 май 12, 22:17    [12582905]     Ответить | Цитировать Сообщить модератору
 Re: Как удалить лишнее из строки?  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
2king2
Замена FOR XML на PIVOT дает ошибку о некорректности синтаксиса использования PIVOT

;WITH
T(ID,F)AS
(
	SELECT 1,N'field100=иванов&field101=иван&field102=4000.00'
	UNION ALL
	SELECT 2,N'field100=петров&field101=петр&field102=5000.00'
	UNION ALL
	SELECT 3,N'field100=простофедя&field101=&field102=666.00'
	UNION ALL
	SELECT 4,N'field100=&field101=&field102=666.00'
)
SELECT * FROM (
SELECT t.id
       ,t1.rn
       ,t1.v
FROM    ( SELECT    id
                   ,CAST(N'<r><f>' + REPLACE(REPLACE(F, N'=', N'</f><c>'), N'&', N'</c><f>') + N'</c></r>' AS XML) s
          FROM      T
        ) t
        CROSS APPLY ( SELECT    UPPER(LEFT(x.z.value('.', 'nvarchar(max)'),1))+RIGHT(x.z.value('.', 'nvarchar(max)'),NULLIF(LEN(x.z.value('.', 'nvarchar(max)')),0)-1) v
                               ,ROW_NUMBER() OVER ( PARTITION BY id ORDER BY ( SELECT   1
                                                                             ) ) rn
                      FROM      s.nodes(N'/r/c') x ( z )
                    ) t1
) t
PIVOT
(
MAX(v)
FOR rn IN ([1], [2], [3])
) AS PivotTable

Разберитесь как работает, пригодится. Ну и PIVOT использовать не обязательно, можно старым добрым MAX(CASE ...) обойтись.
19 май 12, 23:29    [12583245]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить