finding and replac342e - 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

SQL Server 2005 Forums

Transact-SQL (2005)

finding and replac342e

Author

Topic

tytyguy
Starting Member

13 Posts

Posted - 2008-07-19 : 15:43:35

Ok I need some help with a situtation. I have a column that list urls. For example http://www.sadahad.com/link/265235/thisblah/

I am trying to change the number to a different value but the find and replace doesnt seem to find the number. The number in the urls are all the same and the number I need to change all of them to are the same also. So... cliff notes...

I have this: http://www.sadahad.com/link/265235/thisblah/
I want this: http://www.sadahad.com/link/945622/thisblah/

http://www.tunerplaza.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-19 : 17:17:56

SELECT REPLACE(Col1, '265235', '945622') FROM SomeTable

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tytyguy
Starting Member

13 Posts

Posted - 2008-07-19 : 23:54:00

I tried that and get this error...

Msg 8116, Level 16, State 1, Line 1
Argument data type ntext is invalid for argument 1 of replace function.

http://www.tunerplaza.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-20 : 00:47:01

You'll need to use UPDATETEXT then. Check BOL for details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-20 : 02:05:14

http://www.sqlservercentral.com/articles/Miscellaneous/handlingthetextdatatype/985/

tytyguy
Starting Member

13 Posts

Posted - 2008-07-20 : 13:57:26

im kinda new to it. None of those seem to work.

http://www.tunerplaza.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-20 : 14:05:20

Since you are using SQL Server 2005, try to replace TEXT datatype with VARCHAR(MAX).

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-20 : 14:06:44

quote:
Originally posted by tytyguy

im kinda new to it. None of those seem to work.

http://www.tunerplaza.com

not even UPDATETEXT. Did you have a look at sample given in the link?

tytyguy
Starting Member

13 Posts

Posted - 2008-07-20 : 16:25:51

yes. theres 3 different codes. not sure which one I need. I cant find where to put it my values. I want to change '2953976' to '3052631'

http://www.tunerplaza.com

tytyguy
Starting Member

13 Posts

Posted - 2008-07-20 : 18:21:02

this works....

# select cast(replace(cast(mycolumn as nvarchar(max)),'2953976','3052631') as ntext)
# from mytable

http://www.tunerplaza.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-20 : 18:27:22

If you're going to do that, you might as well change the data type of your column. Why are you using ntext?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-20 : 19:22:06

What databas are you using, if # character is part of query?

E 12°55'05.25"
N 56°04'39.16"

tytyguy
Starting Member

13 Posts

Posted - 2008-07-20 : 23:16:35

I retract that. It replaced it but did not insert it back into the column. So its still open that I have no idea what to do.

http://www.tunerplaza.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-20 : 23:21:26

A SELECT does nothing more than return data. It does not update data. If you want to update data, then you must use an UPDATE statement.

But why don't you just change your column's data type? Why did you need ntext?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tytyguy
Starting Member

13 Posts

Posted - 2008-07-21 : 00:13:32

not sure. I did not make the database. Its aspdotnetstorefront ecommerce software. I do not believe it would smart to change it around. Im sure there is a solution for this....

http://www.tunerplaza.com

Thiyagu_04
Starting Member

37 Posts

Posted - 2008-07-21 : 07:13:12

select cast(replace(cast(val as nvarchar(max)),'265235','552355') as ntext)
from tablename

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources