CODEWITHSUNDEEP
sqlsql-server
SHADOWSTRIKE1
SHADOWSTRIKE1

Reputation: 15

SQL to rearrange characters in a string?

I'm hoping someone can point me in the right direction on something... To make a long story short, my work doesn't have a DB Admin, and since I'm over on the server team, somehow SQL DBs have fallen in my lap, though I'm pretty amateur in my experience. Anyway, we have DBs for clients, and a certain varchar(100) column (called Path) contains a string of characters that lists a path to image files in a format like this:

\\ImageServer\YYYY\CLIENTNUMBER\YYYYMMDD\YYYYYMMDD####\

where the "####" is a batch number of a bunch of images so an example would be:

\\ImageServer\2019\0121\20190305\201903050012\

The issue is that this doesn't match our folder structure for this client... they want their last folder to be in "MMDDYYYY####". So that's what we've been outputting them as... which doesn't align with the older SQL data.

Due to this issue, a program we have that reads the SQL database isn't displaying the images, and the easiest fix is going to be alter the SQL database so that the last folder in that string reads in MMDDYYYY#### format instead of YYYYMMDD#### format. Also, this only affects data older than 2020 (when the change took place).

In the end, I want to have that column be like this:

\\ImageServer\YYYY\CLIENTNUMBER\YYYYMMDD\MMDDYYYY####\

which is basically taking that last folder in the path, and changing around the first 8 numbers so instead of "YYYYMMDD####", it is "MMDDYYYY####".

So in that example I posted earlier, this:

\\ImageServer\2019\0121\20190305\201903050012\

would become this:

\\ImageServer\2019\0121\20190305\030520190012\

So I suppose I'm looking for something that would be like:

WHERE (Path LIKE '\\ImageServer\2019\0121\%')

and then some cool code that would rearrange the numbers. Then I'd change the year in that LIKE command and run it for each affected year. I'm just not sure how to rearrange them... Any help in this would be greatly appreciated. I've tried doing some searches, but mostly what I find is how to rearrange specific DateTime data, which this isn't, or how to rearrange strings alphabetically.

Upvotes: 0

Views: 1115

Answers (5)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22157

Please try the following solution. It will work on SQL Server 2017 and later.

It is based on XML and XQuery. Their data model is based on ordered sequences. Exactly what we need for the task.

The algorithm does the following steps:

  1. Tokenizing UNC string based on the backslash as XML data type.
  2. Last token is reformatted to a desired date format via FLWOR expression.
  3. Composing UNC back.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, UNC VARCHAR(100));
INSERT INTO @tbl (UNC) VALUES
('\\ImageServer\2019\0121\20190305\201903050012\'),
('\\ImageServer\2019\0121\20190305\202103090012\');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '\';

SELECT ID, UNC
    , '\\' + REPLACE(TRY_CAST('<root><r><![CDATA[' + 
        REPLACE(TRIM(@separator FROM UNC), @separator, ']]></r><r><![CDATA[') + 
            ']]></r></root>' AS XML)
    .query('
        for $x in /root/r/text()
        return if ($x is (/root/r[last()]/text())[1]) then 
            concat(substring($x,5,4), substring($x,1,4), substring($x,9,4))
            else string($x)
    ').value('.', 'VARCHAR(100)')
    , SPACE(1), @separator) + @separator AS [after]
FROM @tbl;

Output

+----+------------------------------------------------+------------------------------------------------+
| ID |                      UNC                       |                     after                      |
+----+------------------------------------------------+------------------------------------------------+
|  1 | \\ImageServer\2019\0121\20190305\201903050012\ | \\ImageServer\2019\0121\20190305\030520190012\ |
|  2 | \\ImageServer\2019\0121\20190305\202103090012\ | \\ImageServer\2019\0121\20190305\030920210012\ |
+----+------------------------------------------------+------------------------------------------------+

Upvotes: 0

ItalianStallion4215
ItalianStallion4215

Reputation: 138

You could do something like this. This will use CHARINDEX, SUBSTRING, REVERSE, LEN, and LEFT to derive to the final output. This code will be dynamic and will change based on Length of the Path. I put this together real fast, but I am sure it can be cleaned up a bit to make it less complex.

DECLARE @TBL TABLE ([Path] VARCHAR(100)
                    ,[FixedPath] VARCHAR(100));

INSERT INTO @TBL
([Path])
VALUES('\\ImageServer\2019\0121\20190305\201903050012\')
,('\\ImageServer\2020\15896215\20200606\20200606852358\')
,('\\ImageServer\2021\753159\20211209\20211209002585')

UPDATE @TBL 
SET FixedPath = REPLACE([Path]
        ,LEFT(REVERSE(REPLACE(SUBSTRING(REVERSE([Path]), 1, CHARINDEX('\',REVERSE(SUBSTRING([Path],1,LEN([Path])-1)))),'\','')),8) + RIGHT(REVERSE(REPLACE(SUBSTRING(REVERSE([Path]), 1, CHARINDEX('\',REVERSE(SUBSTRING([Path],1,LEN([Path])-1)))),'\','')),LEN(REVERSE(REPLACE(SUBSTRING(REVERSE([Path]), 1, CHARINDEX('\',REVERSE(SUBSTRING([Path],1,LEN([Path])-1)))),'\','')))-8)
        ,FORMAT(CAST(LEFT(REVERSE(REPLACE(SUBSTRING(REVERSE([Path]), 1, CHARINDEX('\',REVERSE(SUBSTRING([Path],1,LEN([Path])-1)))),'\','')),8) AS DATE), 'MMddyyyy') + RIGHT(REVERSE(REPLACE(SUBSTRING(REVERSE([Path]), 1, CHARINDEX('\',REVERSE(SUBSTRING([Path],1,LEN([Path])-1)))),'\','')),LEN(REVERSE(REPLACE(SUBSTRING(REVERSE([Path]), 1, CHARINDEX('\',REVERSE(SUBSTRING([Path],1,LEN([Path])-1)))),'\','')))-8))

SELECT [Path]
        ,[FixedPath]
FROM @TBL

RESULTS

Path FixedPath
\ImageServer\2019\0121\20190305\201903050012\ \ImageServer\2019\0121\20190305\030520190012\
\ImageServer\2020\15896215\20200606\20200606852358\ \ImageServer\2020\15896215\20200606\06062020852358\
\ImageServer\2021\753159\20211209\20211209002585 \ImageServer\2021\753159\20211209\12092021002585

Upvotes: 0

Alan Burstein
Alan Burstein

Reputation: 7918

This will do the trick:

DECLARE @string VARCHAR(1000) = '\\ImageServer\2019\0121\20190305\201903050012\';

SELECT  NewString = 
  STUFF(@string,p.Pos,12,
    CONCAT(SUBSTRING(@string,p.Pos+4,2),SUBSTRING(@string,p.Pos+6,2),
           SUBSTRING(@string,p.Pos,4), SUBSTRING(@string,p.Pos+8,4)))
FROM        (VALUES(PATINDEX('%\'+REPLICATE('[0-9]',12)+'\', @string)+1)) AS p(Pos)
CROSS APPLY (VALUES(SUBSTRING(@string,p.Pos,12)))                         AS dt(Txt);

Returns: \ImageServer\2019\0121\20190305\030520190012\

Against a table:

DECLARE @string VARCHAR(1000) = '\\ImageServer\2019\0121\20190305\201903050012\';

DECLARE @strings TABLE(SomeId INT IDENTITY, OldString VARCHAR(1000));
INSERT @strings VALUES 
  ('\\ImageServer\2019\0121\20190305\201903050012\'),
  ('\\ImageServer\2019\0122\20190305\202005228823\'),
  ('\\ImageServer\2019\0225\20190305\202106119992\');

SELECT
  s.SomeId, s.OldString, f.NewString
FROM @strings AS s
CROSS APPLY 
(
  SELECT  NewString = 
    STUFF(s.OldString,p.Pos,12,
      CONCAT(SUBSTRING(s.OldString,p.Pos+4,2), SUBSTRING(s.OldString,p.Pos+6,2),
             SUBSTRING(s.OldString,p.Pos,4),   SUBSTRING(s.OldString,p.Pos+8,4)))
  FROM        (VALUES(PATINDEX('%\'+REPLICATE('[0-9]',12)+'\',s.OldString)+1)) AS p(Pos)
  CROSS APPLY (VALUES(SUBSTRING(s.OldString,p.Pos,12)))                        AS dt(Txt)
) AS f(NewString);

Results:

SomeId  OldString                                        NewString
------- ------------------------------------------------ ---------------------------------------------------
1       \\ImageServer\2019\0121\20190305\201903050012\   \\ImageServer\2019\0121\20190305\030520190012\
2       \\ImageServer\2019\0122\20190305\202005228823\   \\ImageServer\2019\0122\20190305\052220208823\
3       \\ImageServer\2019\0225\20190305\202106119992\   \\ImageServer\2019\0225\20190305\061120219992\

Upvotes: 1

Tim Mylott
Tim Mylott

Reputation: 2703

This assumes that the last directory in the path is always 12 characters, YYYYMMDD####, and ends with a slash.

Using RIGHT(), SUBSTRING(), CONACT(), STUFF(), REPLACE()

DECLARE @testdata TABLE
(
    [Path] VARCHAR(100)
);

INSERT INTO @testdata (
                          [Path]
                      )
VALUES ( '\\ImageServer\2019\0121\20190305\201903050012\' )
     , ( '\\ImageServer\2019\0121\20190305\201903060012\' )
     , ( '\\ImageServer\2019\0121\20190305\201903070012\' );

SELECT *
     , RIGHT([Path], 13) --Gets the last part of the path
     , SUBSTRING(RIGHT([Path], 13), 1, 4) --Year
     , SUBSTRING(RIGHT([Path], 13), 5, 2) --Month
     , SUBSTRING(RIGHT([Path], 13), 7, 2) --Day
     , CONCAT(SUBSTRING(RIGHT([Path], 13), 5, 2), SUBSTRING(RIGHT([Path], 13), 7, 2), SUBSTRING(RIGHT([Path], 13), 1, 4))  --use above to reformat the date
     --Use Stuff to replace the original YYYYMMDD with the reformatted MMDDYYYY date
     , STUFF(RIGHT([Path], 13), 1, 8, CONCAT(SUBSTRING(RIGHT([Path], 13), 5, 2), SUBSTRING(RIGHT([Path], 13), 7, 2), SUBSTRING(RIGHT([Path], 13), 1, 4)))
     --Then replace
     , REPLACE([Path], RIGHT([Path], 13), STUFF(RIGHT([Path], 13), 1, 8, CONCAT(SUBSTRING(RIGHT([Path], 13), 5, 2), SUBSTRING(RIGHT([Path], 13), 7, 2), SUBSTRING(RIGHT([Path], 13), 1, 4)))) AS UpdatedPath
FROM   @testdata;

Above is just showing how we can break down the string using each of those functions and put it back together to get the updated path:

Path                                                                                                  UpdatedPath
------------------------------------------------- ------------- ---- ---- ---- -------- ------------- -----------------------------------------------
\\ImageServer\2019\0121\20190305\201903050012\    201903050012\ 2019 03   05   03052019 030520190012\ \\ImageServer\2019\0121\20190305\030520190012\
\\ImageServer\2019\0121\20190305\201903060012\    201903060012\ 2019 03   06   03062019 030620190012\ \\ImageServer\2019\0121\20190305\030620190012\
\\ImageServer\2019\0121\20190305\201903070012\    201903070012\ 2019 03   07   03072019 030720190012\ \\ImageServer\2019\0121\20190305\030720190012\

Then the update statement would be something like:

UPDATE @testdata
SET    [Path] = REPLACE([Path], RIGHT([Path], 13), STUFF(RIGHT([Path], 13), 1, 8, CONCAT(SUBSTRING(RIGHT([Path], 13), 5, 2), SUBSTRING(RIGHT([Path], 13), 7, 2), SUBSTRING(RIGHT([Path], 13), 1, 4))))
WHERE  [Path] LIKE '\\ImageServer\2019\0121\%';

SELECT *
FROM   @testdata;


Path
------------------------------------------------
\\ImageServer\2019\0121\20190305\030520190012\
\\ImageServer\2019\0121\20190305\030620190012\
\\ImageServer\2019\0121\20190305\030720190012\

I would suggest ensuring you have up to date backups of the databases and test it in a dev or test environment before running it in your production systems.

Upvotes: 1

SQLpro
SQLpro

Reputation: 5103

This is easy to do with the integrated function CONCAT SUBSTRING and REVERSE :

DECLARE @PATH VARCHAR(100) = '\\ImageServer\2019\0121\20190305\201903050012\'

SELECT CONCAT(LEFT(@PATH, LEN(@PATH) - 13),   
              REVERSE(SUBSTRING(REVERSE(@PATH), 6, 2)),
              REVERSE(SUBSTRING(REVERSE(@PATH), 8, 2)),
              REVERSE(SUBSTRING(REVERSE(@PATH), 10, 4)),
              '\')

Upvotes: 1

Related Questions