Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 sql разбить колонку  [new]
Klok
Guest
Есть колонка вот такого вида
"603155, РОССИЯ, НИЖЕГОРОДСКАЯ обл., г. НИЖНИЙ НОВГОРОД, ул. КОВАЛИХИНСКАЯ, д. 93а, кв. 62". Как разбить её на три колонки, где в одной индекс, во второй страна, а в третьей остальное?
18 окт 17, 14:43    [20879257]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
getred
Member

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

данные в колонке всегда в таком формате написаны?
18 окт 17, 14:46    [20879268]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36989
17841638
18 окт 17, 14:47    [20879273]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
Klok
Guest
getred,

да
18 окт 17, 14:48    [20879283]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
Klok
Guest
Гавриленко Сергей Алексеевич,

У меня больше миллиона строк, нужна сразу все разбить
18 окт 17, 14:50    [20879294]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36989
Klok
Гавриленко Сергей Алексеевич,

У меня больше миллиона строк, нужна сразу все разбить
Разбивайте на здоровье, я не против.

Сообщение было отредактировано: 18 окт 17, 14:51
18 окт 17, 14:51    [20879297]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
iap
Member

Откуда: Москва
Сообщений: 46983
В SQL2016 есть STRING_SPLIT()
18 окт 17, 14:55    [20879316]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
iap
Member

Откуда: Москва
Сообщений: 46983
Klok
Гавриленко Сергей Алексеевич,

У меня больше миллиона строк, нужна сразу все разбить
И что, есть проблемы?
18 окт 17, 14:55    [20879320]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Гавриленко Сергей Алексеевич
Klok
Гавриленко Сергей Алексеевич,

У меня больше миллиона строк, нужна сразу все разбить
Разбивайте на здоровье, я не против.

Взять CSV, нарезать на куски валидные строки, если нет страны или индекса - свалить все в инвалидный CSV, затем порцию валидных циклом залить bulk insert в заранее заготовленную таблицу.
table адресная_куча ( [индекс_6_цифр] [int] [NOT NULL], [название_страны] [nvarchar](200) NOT NULL, [оставшийся_адрес] [nvarchar](255) NOT NULL)
Все, что больше 6+200+255 по длине - заворачивать в инвалидный CSV и задать изготовителю миллиона исходных строк вопрос "Это чо?"
18 окт 17, 14:57    [20879328]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
iap,
ТСу надо не на "слова", а на 3 части :)

Klok,
поиск разделителя в строке - charindex()
получение части из строки - substring()
длина строки - len()
18 окт 17, 14:57    [20879333]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Дедушка
iap,
ТСу надо не на "слова", а на 3 части :)

Klok,
поиск разделителя в строке - charindex()
получение части из строки - substring()
длина строки - len()

Автору темы прилетело откуда-то миллион строк. Он их загнал в таблицу в одной колонкой текстовой. Теперь думает - а как бы перелить в другую таблицу методами SQL, чтобы в другой было 3 колонки и было все хорошо.
Потом выяснится, что среди миллиона строк есть те, где индекса нет, а идет сразу страна.
В общем, я за первичную обработку файла методами скриптовых языков до вставки в SQL. Чтобы "левые" строки сразу компактно запаковались в файл и были отосланы обратно тому, что миллион строк прислал :)
18 окт 17, 15:02    [20879354]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
.NET
Member

Откуда: Москва<-Петушки
Сообщений: 8251
iap
В SQL2016 есть STRING_SPLIT()

string_split возвращает таблицу, с одной колонкой, которая называется [value].
А как ее потом развернуть так, чтобы строки стали столбцами?

Пробую использовать pivot
select *
from string_split('111 222', ' ')
pivot
(
max([value]) 
for [value] in ([1], [2])
) AS PivotTable


получаю
1 |2
NULL|NULL

вместо ожидаемого

1 |2
111|222
10 ноя 17, 10:42    [20942514]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
.NET,
ну имхо в эту сторону, хотя через xml наверное универсальней

SELECT  *
FROM
(
	SELECT
		[rn] = ROW_NUMBER() OVER(ORDER BY 1/0),*
	FROM  string_split('111 222', ' ')
) as Split
pivot
(
	max([value]) 
for [rn] in ([1], [2])
) AS PivotTable
10 ноя 17, 10:47    [20942538]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Klok, если совсем по уму. Сначала бьешь строку на слова по разделителю, а потом делаешь некую нетривиальную (а кому и тривиальную) эвристику, где индекс, а где адрес. С импортом ФИАСА и прочими пингованиями потчы РФ на предмет почтоиндеска.


Либо башляешь сервису, типа Дадата, который делает эту муторную работу за тебя.

Если у тебя сервер ранее 2016, то чем резать на слова есть под спойлером
+

 
   IF (OBJECT_ID(N'dbo.StringToWords', N'IF') IS NULL) 
     BEGIN
 	    EXEC (N'CREATE FUNCTION  dbo.StringToWords() RETURNS TABLE AS RETURN (SELECT a = 1) ') 
     END 
 GO
 
 ALTER FUNCTION dbo.StringToWords
 (
    @String    varchar(MAX) 
  , @Separator varchar(20)
 )
 RETURNS TABLE
 AS
   RETURN
   (
     WITH A AS 
    (

      SELECT 
        Word     = LEFT(
                        @String
                        , CASE 
                            WHEN CHARINDEX (@Separator, @String) > 0 THEN CHARINDEX (@Separator, @String) - 1 
                            ELSE  DATALENGTH(@String) 
                          END
                      ) 
   
      , StartPos = CHARINDEX (@Separator, @String) + DATALENGTH(@Separator) 
      , EndPos   = CHARINDEX (@Separator, @String, CHARINDEX (@Separator, @String) + DATALENGTH(@Separator) )
      , Str      = @String 
      , Chk      = CAST(1 AS bigint) 
      , SepLen   = DATALENGTH(@Separator) 
      , RN       = 1 
      , CharFlag = CHARINDEX (@Separator, @String)  
    UNION ALL 
      SELECT 
        Word    = SUBSTRING(
                             @String, A.StartPos, CASE
                                                    WHEN  A.EndPos - A.StartPos >= 0 THEN A.EndPos - A.StartPos  
                                                    ELSE DATALENGTH(@String)
                                                  END
                            ) 
      , StartPos = A.EndPos + DATALENGTH(@Separator) 
      , EndPos   = CHARINDEX (@Separator, @String, A.EndPos + DATALENGTH(@Separator) )  
      , Str      = @String
      , Chk      = CAST( 
                         CASE 
                           WHEN A.EndPos - A.StartPos >= 0 THEN A.EndPos - A.StartPos  
                           ELSE DATALENGTH(@String)
                         END  AS bigint
                       ) 
      , SepLen   = DATALENGTH(@Separator) 
      , RN       = RN + 1 
      , CharFlag = A.CharFlag 
      FROM
        A  
      WHERE
       A.CharFlag > 0 
       AND A.Chk <> DATALENGTH(@String)
      
     ) 
     SELECT Word = LTRIM(RTRIM(Word)), RN FROM A   
      
   )
 

10 ноя 17, 10:56    [20942615]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
.NET
Member

Откуда: Москва<-Петушки
Сообщений: 8251
TaPaK,

Спасибо это работает.
Так что по теме если есть таблица Customers, например, с двумя колонками

Name - char(50)
Id - int

где в поле Name хранится фамилия, имя, отчество через пробел, то Name можно разбить на 3 колонки
примерно таким запросом
SELECT CustomerId, [1] as Surname, [2] as [Name], [3] as [Patronimic]
from
(
Select [rn] = ROW_NUMBER() OVER(PARTITION BY CustomerId ORDER BY 1/0), [value], CustomerId
	from Customers 
	cross apply string_split(DESCR, ' ')
	) as src
pivot
(
max([value])
for [rn] in ([1], [2], [3])
) as pvt
10 ноя 17, 13:34    [20943445]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
.NET,

главно что-бы у вас не было каких-нибудь "Абдула ибн Хатаб олы" :)
ну и вообще,вариантов
https://www.google.com.ua/search?ei=W4EFWuONJqj-6ASu75OYCA&q=sql split name fullname&oq=sql split name fullname&gs_l=psy-ab.3..35i39k1l2.1294.1431.0.1670.2.2.0.0.0.0.91.181.2.2.0....0...1.1.64.psy-ab..0.2.181...33i160k1.0.bBKJD5STRQM
10 ноя 17, 13:38    [20943473]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
автор
где в поле Name хранится фамилия, имя, отчество через пробел, то Name можно разбить на 3 колонки
примерно таким запросом

Это типовая проблема, и ее надо решать административно и архитектурно, на уровне ввода данных в систему.

Делить Пользователя на три части, это реальный костыль.
У некоторых наций есть только Имя и Отчество, но нет фамилии. Существуют сложные преффиксы типа Иоханес Дидерик ван дер Ваальс. Есть нации у которых цепочка рода указывается дальше чем у нас: Хуан Мария Антонио Де Сильва Де Ла Герра.

Ну и даже если выкинуть иностранцев из логики, даже у нас огромное количество всяких Алиев Ахмед Окталь оглы.
10 ноя 17, 13:51    [20943548]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Cammomile
У некоторых наций есть только Имя и Отчество, но нет фамилии. Существуют сложные преффиксы типа Иоханес Дидерик ван дер Ваальс. Есть нации у которых цепочка рода указывается дальше чем у нас: Хуан Мария Антонио Де Сильва Де Ла Герра.

Ну и даже если выкинуть иностранцев из логики, даже у нас огромное количество всяких Алиев Ахмед Окталь оглы.


Да, Вы правы. И даже не определить, как нашего министра обороны правильно именовать - "...В действительности, родовой фамилией Сергея Шойгу была не Шойгу, а Кужугет. Эта путаница возникла при получении паспорта его отцом – имя и фамилию поменяли местами...", а ведь тоже сидели программистыклерки и думали, что смогут быстро на три части ФИО разбить :)
Я уже не говорю, как будет классно звучать обращение к испанцу-клиенту из приведенного примера - "дорогой Мария Хуанович"...
10 ноя 17, 13:57    [20943585]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
iap
Member

Откуда: Москва
Сообщений: 46983
Почему вы забыли о китайских, вьетнамских, корейских именах-фамилиях?
10 ноя 17, 14:02    [20943612]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
А почему мы забыли? Или это ирония?

Я, например, работал в конторе где было немало иностранных клиентов. В какой то момент это стало большой проблемой, там на стадии проектировки системы какой то шизик додумался сделать ФИО: Варчар(200). Спустя несколько лет оказалось, что надо распарсить несколько десятков тысяч клиентов, среди которых немало всяких Лю Ваней или Снорри Торвальсонов...
Ну и конечно весь набор артефактов из постсоветского пространства. Оглы, Джан и всё такое...

Поэтому я и пишу, что этот вопрос надо решать заранее.
10 ноя 17, 14:06    [20943630]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
iap
Member

Откуда: Москва
Сообщений: 46983
Вспомнилось, кстати: Кто лучше? (Фамилия Имя Отчество - > Фамилия И. О. )
10 ноя 17, 14:14    [20943665]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Andy_OLAP
Я уже не говорю, как будет классно звучать обращение к испанцу-клиенту из приведенного примера - "дорогой Мария Хуанович"

в испанском НЕТ отчеств, дорогой эксперт Хуанович
10 ноя 17, 14:25    [20943742]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
.NET
Member

Откуда: Москва<-Петушки
Сообщений: 8251
автор
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
Oct 28 2016 18:17:30
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 15063: ) (Hypervisor)

CREATE TABLE #Customers
(
	Id int,
	Name varchar(50)
)

INSERT INTO #Customers VALUES (1, 'IVANOV IVAN IVANOVICH')
INSERT INTO #Customers VALUES (2, 'PETROVA MARIA SERGEEVNA')
INSERT INTO #Customers VALUES (3, 'CIDOROV FIODOR FIODOROVICH')

SELECT Id, [1], [2], [3] 
FROM
(SELECT *, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY 1/0) AS [RN]
	FROM #Customers
	CROSS APPLY STRING_SPLIT(Name, ' ') 
	) SRC
PIVOT
(
	MAX([Value])
	FOR [RN] IN ([1], [2], [3])
) PVT
11 ноя 17, 18:26    [20945920]     Ответить | Цитировать Сообщить модератору
 Re: sql разбить колонку  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
И зачем нам тут пример стринг сплита? Который, кстати, не решает вашу задачу.
13 ноя 17, 10:24    [20948184]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить