SQL Substring and Charindex-CodePudding

I have 111-1001-0000-0000 this record in one column and 453200-0000-000 in second column

I want output as 111-1001-0000-453200-0000-0000-000

That means 111-1001-0000 from 1st column and 453200 from 2nd column and again 0000 from 1st column and 0000-000 from 2nd column

I tried below query but getting 111-1001-453200-0000-0000-000.

-0000 is missing from 1st column

Declare @1stcolumn nvarchar(30),@2ndcolumn nvarchar(30)

set @1stcolumn='111-1001-0000-0000'
            
set @2ndcolumn='453200-0000-000' 

select substring(@1stcolumn,1,charindex(right(@1stcolumn,charindex('-',reverse(@1stcolumn))),@1stcolumn))
 substring(@2ndcolumn,1,charindex('-',@2ndcolumn)) reverse(substring(reverse(@1stcolumn),0,charindex('-',reverse(@1stcolumn))))
 '-' substring(@2ndcolumn,charindex('-',@2ndcolumn) 1,len(@2ndcolumn))

CodePudding user response：

find the position where to split column 1 and column2. Use LEFT() and RIGHT() to split the string and then concatenate back in the order that you want

; with tbl as
(
    select  col1 = @1stcolumn, col2 = @2ndcolumn
)
select  *,
        c1.s1   '-'   c2.s1   '-'  c1.s2   '-'   c2.s2
from    tbl t
        cross apply
        (
            select  s1 = left(col1, p - 1),
                    s2 = right(col1, len(col1) - p)
            from    (
                        -- find the position of 3rd '-' by cascading charindex
                        select  p = charindex('-', col1, 
                                         charindex('-', col1, 
                                             charindex('-', col1)   1)   1)
                    ) p
        ) c1
        cross apply
        (
            select  s1 = left(col2, p - 1),
                    s2 = right(col2, len(col2) - p)
            from    (
                        select  p = charindex('-', col2)
                    ) p
        ) c2

CodePudding user response：

A little modification in first substring. To get correct length I used LEN .

select substring(@1stcolumn,1,(Len(@1stcolumn) - charindex('- ',REVERSE(@1stcolumn))   1))
 substring(@2ndcolumn,1,charindex('-',@2ndcolumn))
 reverse(substring(reverse(@1stcolumn),0,charindex('-',reverse(@1stcolumn))))
 '-' substring(@2ndcolumn,charindex('-',@2ndcolumn) 1,len(@2ndcolumn))

CodePudding user response：

I'd probably do with with PARSENAME as it's quite concise then:

WITH YourTable AS(
    SELECT '111-1001-0000-0000' AS Column1,
           '453200-0000-000' AS Column2)
SELECT CONCAT_WS('-',PN.C1P1,PN.C1P2,PN.C1P3,PN.C2P1,PN.C1P4,PN.C2P2,PN.C2P3) AS NewString
FROM YourTable YT
     CROSS APPLY (VALUES(REPLACE(YT.Column1,'-','.'),REPLACE(YT.Column2,'-','.')))R(Column1,Column2)
     CROSS APPLY (VALUES(PARSENAME(R.Column1,4),PARSENAME(R.Column1,3),PARSENAME(R.Column1,2),PARSENAME(R.Column1,1),PARSENAME(R.Column2,3),PARSENAME(R.Column2,2),PARSENAME(R.Column2,1)))PN(C1P1,C1P2,C1P3,C1P4,C2P1,C2P2,C2P3);

CodePudding user response：

WITH
  test AS
(
select '111-1001-0000-0000' as col1, '453200-0000-000' as col2
)
,cte as
(
select 
       col1,
       col2,
       substring
                          (
                          col1,
                          0,
                          len(col1)-charindex('-',reverse(col1))
                          ) as part1,
        substring
                          (
                          col2,
                          0,
                          len(col2)-charindex('-',col2) - 1
                          ) as part2
from test
),
cte2 as
(
select 
        part1,
        part2,
        substring
                          (
                          reverse(col1),
                          0,
                          charindex('-',reverse(col1))
                          ) as part3,
        substring
                          (
                          col2,
                          charindex('-',col2) 1,
                          len(col2)-charindex('-',col2) 1
                          ) as part4
from cte
)
select part1 '-' part2 '-' part3 '-' part4
from cte2