November 22, 2010 at 4:37 pm
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
November 22, 2010 at 5:25 pm
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?
November 22, 2010 at 10:01 pm
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
November 23, 2010 at 5:50 pm
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
November 23, 2010 at 6:35 pm
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
November 23, 2010 at 11:11 pm
luissantos (11/23/2010)
Hello Sachin and Asparnathe 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply