Replace Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Replace Query

Author

Topic

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2008-02-20 : 06:33:34

I have table data field like this. (type text)

TAZ1
-----------
SM1-01
SM1-04
SM1-03
....

SM10-18
SM10-21
SM10-05
SM13-06
SM13-07
....
..
SM13-09
SM14-07
SM14-08
...
...

I want to write replace query that when replace query run
it write all data to TAZ2 field like this

TAZ2
-----
101
104
103
...

1306
1307
...
..

1407
1408
...
..

i.e. replace MS and - (hyphen) and write data (only number, digit)
to TAZ2 field live above ?

Thanks and regards
Mateen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 06:41:38

TAZ2=SUBSTRING(REPLACE(TAZ1,'-',''),3,LEN(REPLACE(TAZ1,'-','')))

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2008-02-20 : 06:59:38

Thanks for your response.

it give syntax error
Line 1: Incorrect syntax near '='.

(sorry, field type is nvarchar and table name TEST)

Thanks
Mateen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-20 : 07:15:44

It works perfectly fine for me

declare @t table
(
	taz1 nvarchar(200)
)

insert into @t
select 'SM1-01' union all
select 'SM1-04' union all
select 'SM1-03' union all
select 'SM10-18'  union all
select 'SM10-21'  union all
select 'SM10-05'  union all
select 'SM13-06'  union all
select 'SM13-07'  union all
select 'SM13-09'  union all
select 'SM14-07'  union all
select 'SM14-08'


select TAZ2=SUBSTRING(REPLACE(TAZ1,'-',''),3,LEN(REPLACE(TAZ1,'-','')))
from @t

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-20 : 08:39:48

or
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-20 : 08:40:56

quote:
Originally posted by mateenmohd

Thanks for your response.

it give syntax error
Line 1: Incorrect syntax near '='.

(sorry, field type is nvarchar and table name TEST)

Thanks
Mateen

You should have run that as SELECT statement

Madhivanan

Failing to plan is Planning to fail

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2008-02-20 : 08:47:45

Thanks you very much.

I have to add the records retrieve by above replace query to same table into another
colum, what will be insert / update query ?

ie. it retreive the record as above query and insert
into another colum in front of same records.
ie.

TAZ1.....TAZ2
----------------
SM1-01.. 101
SM1-04.. 104
SM1-03.. 103
....

Thanks
Mateen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-20 : 08:51:05

SELECT SUBSTRING(REPLACE(TAZ1,'-',''),3,LEN(REPLACE(TAZ1,'-',''))) as TAZ2 from your_table

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-20 : 08:53:28

[code]Update Table
Set Taz2 = SUBSTRING(REPLACE(TAZ1,'-',''),3,LEN(REPLACE(TAZ1,'-','')))
where taz1 like 'SM[0-9]%-[0-9]%'
[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2008-02-20 : 09:16:23

Thanks you very much.

good query.

Mateen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources