0

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
Improve this question
3
  • This is a fairly tall task in SQL Server, because it doesn't have any native regex replacement support. I don't have an answer, but maybe you could use another tool for this (or a UDF). Commented Jan 23, 2018 at 6:26
  • My advice here, if you must have it done with SQL script only then perfect solution is to create UDF on C# see learn.microsoft.com/en-us/sql/relational-databases/… Commented Jan 23, 2018 at 9:35
  • I have updated my question with the approach i'm trying. Commented Jan 24, 2018 at 7:20

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I liked the approach you suggested,but for my case it fails in some scenarios.I have updated my question with the ans i'm trying ,can you let me know the syntax error in my replace query.
Glad to help :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.