Replacing only numeric value on a string

Question

Post reply

Replacing only numeric value on a string

luissantos

SSCertifiable

Points: 7391
More actions
November 22, 2010 at 4:37 pm

#231441

Hello comunity
I need to know how to any numeric value on a string value field like:
"my invoice number 3688992."
I need to make an update statment to replace only 3688992 to another value , also numeric or string but diferent like :
"my invoice number 456"
How can do that on tsql update script.
MAny thanks
Luis Santos

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Aparna-1 SSCrazy Points: 2339 More actions · Answer 1

Hi,

Not quite understand your requirement, but is this what you are trying to do:

[Code]

declare @numeric1 int

declare @numeric2 int

set @numeric1=3688992

set @numeric2 = 456

declare @x table

(

string varchar(255)

)

insert into @x(string)

select

'my invoice number 3688992'

union all

select

'my invoice number 34'

select * from @x

UPDATE

@x

SET

string = REPLACE(string,@numeric1,@numeric2)

SELECT * FROM @x

[/Code]

What about if there is a string like my invoice number 34?

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 2

declare @STR varchar(50)='my invoice number 3688992'

declare @num varchar(max)=''

declare @value varchar(50)='456'

select @STR

select @num=@num+''+n from

(

select SUBSTRING(@str,number,1)n from master..spt_values

where number<LEN(@str)and type='P'

)T where n between '0' and '9'

select REPLACE(@str,@num,@value)

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

luissantos SSCertifiable Points: 7391 More actions · Answer 3

Hello Sachin and Asparna

the script of Sachin is quiet perfect , but the unique problem is the result :

my invoice number 4562

i dont need the last number "2" of the original string "my invoice number 3688992" from your previous select, all i need is changing the text to :

my invoice number 456

Also , Sachin i can resolve this and how to make an Update to this Field.

Asparma, your script return what i need if i run it like that:

declare @numeric1 int

declare @numeric2 int

set @numeric1=3688992

set @numeric2 = 456

declare @x table

(

string varchar(255)

)

insert into @x(string)

select

'my invoice number 3688992'

--union all

--select

-- 'my invoice number 34'

--select * from @x

UPDATE

@x

SET

string = REPLACE(string,@numeric1,@numeric2)

SELECT * FROM @x

Many thanks from both, and if you have an ideia Sachin , i will appreciate your forward.

Best regards

Luis Santos

WayneS SSC Guru Points: 95460 More actions · Answer 4

Luis,

Assuming:

1. That the numbers to be replaced are the only place in the string that any numbers are, and

2. That you want what is to the left of it, plus the new numbers

And borrowing from Aparna, how does this work for you?

declare @numeric2 int

set @numeric2 = 456

declare @x table(string varchar(255));

insert into @x(string)

select 'my invoice number 3688992' union all

select 'my invoice number 34';

select * from @x;

UPDATE @x

SET string = left(string, PATINDEX('%[0-9]%', string)-1) +

convert(varchar(11), @numeric2);

SELECT *

FROM @x;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 5

luissantos (11/23/2010)
Hello Sachin and Asparna
the script of Sachin is quiet perfect , but the unique problem is the result :
my invoice number 4562
i dont need the last number "2" of the original string "my invoice number 3688992" from your previous select, all i need is changing the text to :
my invoice number 456
Also , Sachin i can resolve this and how to make an Update to this Field.

Sorry missed the "=" part in the comparision near LEN function.

declare @STR varchar(50)='my invoice number 3688992'

declare @num varchar(max)=''

declare @value varchar(50)='456'

select @STR

select @num=@num+''+n from

(

select SUBSTRING(@str,number,1)n from master..spt_values

where number<=LEN(@str)and type='P'

)T where n between '0' and '9'

select REPLACE(@str,@num,@value)

As far the update is concerned create a function using above query & update your column using the function.

Something like this

Update yourtable set fieldtobereplaced=yourfunction(fieldtobereplaced,valuetoberplacedwith)

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server