CODEWITHSUNDEEP
sqlsql-serverdatabase
Anu
Anu

Reputation: 39

SQL append in middle of column data in update query

When we do data refresh we need to append \\prod01\\Test\Load server name with \\prod01.qa.com\\Test\Load

How do I write update query for this. There can be different server names all I need to do is write update script to append server name with qa.com

This is my query that gives all results that have server location.

select * from AppSetting where Value like '%\\%\%' or Value like '%//%/%';

My Prod data looks like this 

Value
\\prod01\Images\Load
\\prod01prod6253\Images\Load
\\server05ser\Images\Delete
\\pgdg1076\Email
\\pgdg1076ythg\Test\Load
http://prod7/delta/

My QA data should looks like this after update query

Value
\\prod01.qa.com\Images\Load
\\prod01prod6253.qa.com\Images\Load
\\server05ser.qa.com\Images\Delete
\\pgdg1076.qa.com\Email
\\pgdg1076ythg.qa.com\Test\Load
http://prod7.qa.com/delta/

This is the update query I have. Can I write a generic query

UPDATE eroom.AppSetting
SET Location = REPLACE(Location, '\\prod01\', '\\prod01.qa.tbc.com\') 
WHERE Location like '%\\prod01\%';

UPDATE eroom.AppSetting
SET Location = REPLACE(Location, '\\server05ser\', '\\server05ser.qa.tbc.com\') 
WHERE Location like '%\\server05ser\%';

Upvotes: 0

Views: 327

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

This answers the original version of the question.

The simplest way might be with stuff() and a case expression:

select (case when location like '\\prod[0-9][0-9]\*'
             then stuff(location, 9, 0, '.qa.tbc.com'
             else location
        end)

The "prod" portion looks to be fixed length, so you don't need to search for a pattern.

Upvotes: 0

Thom A
Thom A

Reputation: 95554

I'm posting this as a new answer, as the OP moved the goal posts quite a bit. Instead, I now use CHARINDEX to find the location of each slash (forward or back). As fortunately the injection needs to happen before the 3rd slash, we can use that to our advantage:

SELECT STUFF(V.Value,CI3.I,0,'.qa.tbc.com') AS NewValue,*
FROM (VALUES('\\prod01\Images\Load'),
            ('\\prod01prod6253\Images\Load'),
            ('\\server05ser\Images\Delete'),
            ('\\pgdg1076\Email'),
            ('\\pgdg1076ythg\Test\Load'),
            ('http://prod7/delta/'))V([value])
     CROSS APPLY (VALUES(CASE WHEN V.[value] LIKE '%/%' THEN '/' ELSE '\' END)) L(C) --So I don't have to keep checking what character I need
     CROSS APPLY (VALUES(CHARINDEX(L.C,V.[value]))) CI1(I)
     CROSS APPLY (VALUES(CHARINDEX(L.C,V.[value],CI1.I+1))) CI2(I)
     CROSS APPLY (VALUES(CHARINDEX(L.C,V.[value],CI2.I+1))) CI3(I);

Upvotes: 1

Thom A
Thom A

Reputation: 95554

This is one method. I put the expressions for the PATINDEX and CHARINDEX in the FROM, as I find it far easier to read, and means less repetition:

SELECT V.[value],
       ISNULL(STUFF(V.Value,ISNULL(CI.fs,CI.bs),0,'.qa.tbc.com'),V.[value]) AS NewValue
FROM (VALUES('\\prod01\Images\Load'),
            ('\\prod05\Images\Delete'),
            ('\\prod10\Email'),
            ('//http://prod7/delta/'))V([value])
     CROSS APPLY (VALUES(NULLIF(PATINDEX('%prod[0-9]%',V.value),0)))PI(I)
     CROSS APPLY (VALUES(NULLIF(CHARINDEX('/',V.[value],PI.I),0),NULLIF(CHARINDEX('\',V.[value],PI.I),0))) CI(fs,bs);

Upvotes: 0

Related Questions