CODEWITHSUNDEEP
sqlsql-servert-sql
Dilip
Dilip

Reputation: 155

Rearrange a text into a different order using SQL

I have a text RAW/+A/@B/abs.m>C and it has to be converted to A.B.C

Could someone help me with an efficient SQL. I am struggling to build one.

This is the rule to be followed - use whatever is to the right of '>' preceded by whatever comes after each '+' or '@'

e.g. fixedpoint/fixedpoint/+embedded/@fi/abs.m>abs becomes embedded.fi.abs

Upvotes: 1

Views: 63

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

Just PoC that uses recursive CTE:

WITH cte AS (
  SELECT  id
         ,orig   = c
         ,result = CAST('' AS VARCHAR(MAX))
         ,i      = PATINDEX('%[+@>]%', c)
         ,s      = c
  FROM tab
  UNION ALL
  SELECT c.id,
         c.orig,
         c.result+'.'+CAST(LEFT(sub.z,IIF(PATINDEX('%[/]%',sub.z)-1 < 0, LEN(c.s),
                    PATINDEX('%[/]%',sub.z)-1)) AS VARCHAR(MAX)),
         PATINDEX('%[+@>]%', sub.z),
         sub.z
  FROM cte c
  CROSS APPLY (VALUES (SUBSTRING(c.s, i+1, LEN(c.s)))) AS sub(z)
  WHERE PATINDEX('%[+@>]%', c.s) <> 0
)
SELECT TOP 1 WITH TIES orig, STUFF(result,1,1,'') AS f
FROM cte
ORDER BY ROW_NUMBER() OVER(PARTITION BY id ORDER BY LEN(result) DESC);

DBFiddle Demo

Output:

┌───────────────────────────────────────────────┬─────────────────────┐
│                     orig                      │          f          │
├───────────────────────────────────────────────┼─────────────────────┤
│ RAW/+A/@B/abs.m>C                             │ A.B.C               │
│ fixedpoint/fixedpoint/+embedded/@fi/abs.m>abs │ embedded.fi.abs     │
│ fasfasfds/@ABC/asdsa/+eqwe/@afdsda/asdsa>XYZ  │ ABC.eqwe.afdsda.XYZ │
│ some_text                                     │ null                │
└───────────────────────────────────────────────┴─────────────────────┘

Upvotes: 0

ccarpenter32
ccarpenter32

Reputation: 1077

Although I am sure you have other variations, this would suffice for your example:

;WITH CTE (Column1) AS (
    SELECT * FROM (
        VALUES
            ('RAW/+A/@B/abs.m>C')
    ) AS A (Column1)
)

SELECT SUBSTRING(Column1, CHARINDEX('+', Column1) + 1, 1) + '.' +
SUBSTRING(Column1, CHARINDEX('@', Column1) + 1, 1) + '.' +
SUBSTRING(Column1, CHARINDEX('>', Column1) + 1, 1) AS Result
FROM CTE

It would assume there are only one of each of the special characters as well as only one of each of the "to find" characters.

Upvotes: 0

S3S
S3S

Reputation: 25112

If you only have one instance of each special character + @ > then the below will work.

declare @table table (c1 varchar(64))
insert into @table
values
('RAW/+A/@B/abs.m>C '),
('fixedpoint/fixedpoint/+embedded/@fi/abs.m>abs')

;with cte as(
select
    fst = substring(c1,charindex('+',c1) + 1,charindex('/',substring(c1,charindex('+',c1) + 1,8000)) - 1)
    ,mid = substring(c1,charindex('@',c1) + 1,charindex('/',substring(c1,charindex('@',c1) + 1,8000)) - 1)
    ,lst = substring(c1,charindex('>',c1) + 1,8000)
from
    @table)

select
    fst + '.' + mid + '.' + lst
from cte

Upvotes: 1

Related Questions