CODEWITHSUNDEEP
sql-servert-sql
Vishal
Vishal

Reputation: 2017

Replacing part of the string in SQL with different patterns

Problem Statement:

I need to replace part of the string from the address column in SQL query(script),preferably phone number, where i could see around 91 patterns.What's is the best way to achieve this.?

Earlier I had the similar issue but I was dealing with only 5 different patterns, so I was able to do this by using nested replace like: replace(replace(replace()))

But now I have around 91 different patterns to update, so I was looking for the better way to accomplish this. I'm also dealing here with data of around 10000 rows.

Basically I need to update phone numbers for Wells Fargo as: 800-848-9492

Actual Data for 2 rows in Address Column looks like:

  1. Wells Fargo: 800-841-7254 Huntington Mortgage: 800-262-4532
  2. Wells Fargo: (800) 456-7654 Huntington Mortgage: (800) 451-8763

91 patterns looks similar to below:(Common thing we have is Wells Fargo)

  1. Wells Fargo: 800-841-7254

  2. Wells Fargo: (800) 456-7654

  3. Wells Fargo: 1-866-876-1234

  4. Wells Fargo: (800) 654-7654

  5. Wells Fargo: 866-213-3211

These should be replaced as:

  1. Wells Fargo: 800-848-9492

  2. Wells Fargo: 800-848-9492

  3. Wells Fargo: 800-848-9492

  4. Wells Fargo: 800-848-9492

  5. Wells Fargo: 800-848-9492

Actual Data for 2 rows in Address Column should look like:

  1. Wells Fargo: 800-848-9492 Huntington Mortgage: 800-262-4532

  2. Wells Fargo: 800-848-9492 Huntington Mortgage: (800) 451-8763

I'm trying to achieve this by using the Table variable with Phone Numbers and replacing like below:

Replace in the below query is giving syntax error like multi-part identifier could not be found.

Do I need to make some kind of join with table variable..?? 

DECLARE @tblPhoneNumbers table (data varchar(100))
Insert @tblPhoneNumbers select '800-841-7254'
Insert @tblPhoneNumbers select '1-866-876-1234'
Insert @tblPhoneNumbers select '(800) 654-7654'


Update VDV SET 
VDV.AttribValueTxt=REPLACE(VDV.AttribValueTxt,P1.data,'800-848-9492') from @tblPhoneNumbers P1,
VDV.UpdateDt=VDV.UpdateDt,
VDV.UpdateId=@UpdateId
FROM [dbo].[tblCliPolVerProdDeliveryAttribValue](NOLOCK) AS VDV
INNER JOIN [dbo].[tblCliVerProdDelttribute](NOLOCK) as VDA ON VDV.AttribId=VDA.AttribId
INNER JOIN [dbo].[tblPolMasDelAttribute](NOLOCK) as MDA ON VDA.AttribId=MDA.AttribId
Where MDA.AttribId IN(1246,1270) AND DeliveryAttribValueTxt IS NOT NULL

Upvotes: 0

Views: 143

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

Update

A replace using an inner join to a table variable will also work, but then you need to insert all the parts of the string you want to replace into that table variable.

Basically, the statment would be something like this (based on the statment you posted in the question):

UPDATE VDV 
SET VDV.AttribValueTxt = REPLACE(VDV.AttribValueTxt,P1.data,'800-848-9492'),
    VDV.UpdateDt = VDV.UpdateDt,
    VDV.UpdateId = @UpdateId
FROM [dbo].[tblCliPolVerProdDeliveryAttribValue](NOLOCK) AS VDV
INNER JOIN [dbo].[tblCliVerProdDelttribute](NOLOCK) AS VDA ON VDV.AttribId=VDA.AttribId
INNER JOIN [dbo].[tblPolMasDelAttribute](NOLOCK) AS MDA ON VDA.AttribId=MDA.AttribId
INNER JOIN @tblPhoneNumbers AS P1 ON VDV.AttribValueTxt LIKE '%'+ P1.data +'%'
WHERE MDA.AttribId IN(1246,1270) 
AND DeliveryAttribValueTxt IS NOT NULL

You can see a live demo of this approach on rextester.

First version

Assuming I Understand your question, all rows starts with Wells Fargo:, followed by a phone number with different formats, followed by some non-numeric text.

I think I would probably go with a combination of stuff and patindex here.
Something like this:

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
    Address nvarchar(100)
)

INSERT INTO @T(Address) VALUES
('Wells Fargo: 800-841-7254 Huntington Mortgage: 800-262-4532'),
('Wells Fargo: (800) 456-7654 Huntington Mortgage: (800) 451-8763'),
('Wells Fargo: 1-866-876-1234 Some text here... (800) 832-9203')

The update statement:

UPDATE @T
SET Address = STUFF(Address, 13, PATINDEX('%[0-9] [a-z]%', Address)-11, ' 800-848-9492 ')

Test:

SELECT Address
FROM @T

Results:

Address
Wells Fargo: 800-848-9492 Huntington Mortgage: 800-262-4532
Wells Fargo: 800-848-9492 Huntington Mortgage: (800) 451-8763
Wells Fargo: 800-848-9492 Some text here... (800) 832-9203

You can see a live demo on rextester.

Upvotes: 1

Related Questions