May 13, 2014 at 8:47 am
Hi all,
Here's my situation:
Large design flaw, I know, but we have a table with a varchar field which contains alpha - numeric values. I need to change just the numerics, which can be in an position on the field:
Sample data of the field:
Rec1: Sally Morgan 201-555-1212
Rec2: 555-4040 John Smith
Rec3: Jane Houstin 201-555-6452 ext1223
Desired result:
Rec1: Sally Morgan 999-999-9999
Rec2: 999-9999 John Smith
Rec3: Jane Houstin 999-999-9999 ext9999
There's a bunch of UDFs out there for selecting just the numerics, but I'm having trouble throwing it into a viable script for repeatable execution.
Appreciate any help.
Thanks in Advance!
May 13, 2014 at 9:04 am
You could use the REPLACE function. You would need to use it for each number you want to replace with the character 9.
DECLARE @s-2 VARCHAR(50)
SET @s-2 = 'Sally Morgan 201-555-1212'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@S,'1','9'),'2','9'),'3','9'),'4','9'),'5','9'),'6','9'),'7','9'),'8','9'),'0','9')
May 13, 2014 at 9:06 am
So the numerics might have hyphens?
You can use LIKE with patterns. e.g.
WHERE somecolumn LIKE '%[0-9]%' AND somecolumn NOT LIKE '%[A-Za-z]%'
should match rows that have numbers but no alphabetical characters
May 13, 2014 at 9:06 am
Thanks! I was hoping to be able to do it all with one script. But REPLACE might be the way to go.
Appreciate the help.
May 13, 2014 at 9:10 am
lbrady (5/13/2014)
Hi all,Here's my situation:
Large design flaw, I know, but we have a table with a varchar field which contains alpha - numeric values. I need to change just the numerics, which can be in an position on the field:
Sample data of the field:
Rec1: Sally Morgan 201-555-1212
Rec2: 555-4040 John Smith
Rec3: Jane Houstin 201-555-6452 ext1223
Desired result:
Rec1: Sally Morgan 999-999-9999
Rec2: 999-9999 John Smith
Rec3: Jane Houstin 999-999-9999 ext9999
There's a bunch of UDFs out there for selecting just the numerics, but I'm having trouble throwing it into a viable script for repeatable execution.
Appreciate any help.
Thanks in Advance!
Are you just trying to change all numbers to 9? The easiest way to do this is with some nested replaces.
Something like this.
create table #Something
(
SomeValue varchar(50)
)
insert #Something
select 'Sally Morgan 201-555-1212' union all
select '555-4040 John Smith' union all
select 'Jane Houstin 201-555-6452 ext1223'
select REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SomeValue, '8', '9'), '7', '9'), '6', '9'), '5', '9'), '4', '9'), '3', '9'), '2', '9'), '1', '9'), '0', '9')
from #Something
drop table #something
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 13, 2014 at 9:10 am
From what you've already posted I think you can do it one script. What makes you think you can't?
May 13, 2014 at 9:10 am
LOL. tripleAxe AND gbritton1 posted while I got pulled away. 🙂
--edit--
I missed the tripleAxe post in my first reply.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 13, 2014 at 10:09 am
You all are totally awesome. Got what I needed! Thanks so much
May 13, 2014 at 10:11 am
tripleAxe (5/13/2014)
From what you've already posted I think you can do it one script. What makes you think you can't?
Because my brain isn't properly functioning, today...
:hehe:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply