Разделить записи в столбце, разделенном разделителем

Question

У меня есть проблема, с которой мне нужна помощь. У меня есть столбец с набором строк, которые разделены "." (Пример ниже) У меня есть запрос, чтобы разделить его с помощью подстроки и charindex, проблема в том, что второй разделитель не всегда имеет одинаковую длину, поэтому, когда я это делаю, он не разделяется правильно.

Column A
100.11111.12.16
101.36333
101.REMOTE.01
200.ACTIVE

Мне нужно, чтобы вышеуказанные данные были разбиты на 3 столбца.

Column A           Column B      Column C     Column D
100.11111.12.16    100           11111          12.16
101.36333          101           36333  
101.REMOTE.01      101           REMOTE          01
200.ACTIVE         200           ACTIVE

Как вы можете видеть, второй разделитель не всегда попадает в одну и ту же позицию всегда. Первое всегда после 3, поэтому я могу разделить его, но во втором случае возникают проблемы с разделением, потому что я не могу определить точное местоположение.

Answer 1

Для этого решения необходимо знать о функции ISNULL. Ссылки на функции SUBSTRING, NULLIF, CHARINDEX были даны ранее уважаемым @Pரதீப். Функция LEN говорит сама за себя.

SELECT a [Column A],
  SUBSTRING(a, 1, ISNULL(p1 - 1, LEN(a))) [Column B],
  SUBSTRING(a, p1 + 1, ISNULL(p2, LEN(a) + 1) - p1 - 1) [Column C],
  SUBSTRING(a, p2 + 1, LEN(a) - p2) [Column D]
FROM(VALUES('100.11111.12.16'),
           ('101.36333'),
           ('101.REMOTE.01'),
           ('200.ACTIVE'),
           ('42170')) a(a)
CROSS APPLY(VALUES(NULLIF(CHARINDEX('.', a), 0))) b(p1)
CROSS APPLY(VALUES(NULLIF(CHARINDEX('.', a, p1 + 1), 0))) c(p2)
CROSS APPLY(VALUES(NULLIF(CHARINDEX('.', a, p2 + 1), 0))) d(p3);

Проверьте это на http://rextester.com/TOKRH13046.

Answer 2

Вот один из способов использования строковых функций

SELECT [Column A] = t.string, 
       [Column B] = LEFT(t.string, Charindex('.', t.string) - 1), 
       [Column C] = Reverse(Parsename(Reverse(cs.string), 1)), 
       [Column D] = Stuff(cs.string, 1, NULLIF(fpos, 0), '') 
FROM   Yourtable t 
       CROSS apply(VALUES (Substring(string, Charindex('.', string) + 1, Len(string))))cs(string) 
       CROSS apply(VALUES (Charindex('.', cs.string))) p (fpos) 

• Rextester Demo

Ссылки MSDN:

• Оставил
• Substring
• CHARINDEX
• дрянь
• Задний ход
• ParseName
• NULLIF

Answer 3

Еще один вариант использования небольшого XML в сочетании с CROSS APPLY

пример

Declare @YourTable Table ([Column A] varchar(50))
Insert Into @YourTable Values 
 ('100.11111.12.16')
,('101.36333')
,('101.REMOTE.01')
,('200.ACTIVE')

Select A.* 
      ,[Column B] = B.Pos1
      ,[Column C] = B.Pos2
      ,[Column D] = substring(concat('.'+B.Pos3,'.'+B.Pos4),2,50)
 From  @YourTable A
 Cross Apply (
                Select Pos1 = xDim.value('/x[1]','varchar(max)')
                      ,Pos2 = xDim.value('/x[2]','varchar(max)')
                      ,Pos3 = xDim.value('/x[3]','varchar(max)')
                      ,Pos4 = xDim.value('/x[4]','varchar(max)')
                From  (Select Cast('<x>' + replace(A.[Column A],'.','</x><x>')+'</x>' as xml) as xDim) as X
             ) B

Возвращает

Answer 4

Если максимальное количество точек равно 3, тогда parsename может выполнить всю работу:

select columnA,
       parsename(reverse(columnA),1) columnB,
       parsename(reverse(columnA),2) columnC,
       parsename(reverse(columnA),3) + isnull('.'+parsename(reverse(columnA),4),'')  columnD
from table