Partial SQL Update/replace script question

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

Partial SQL Update/replace script question

Author

Topic

jsnyder
Starting Member

3 Posts

Posted - 2007-04-10 : 14:31:16

I need to do an update to several tables that would update just a few characters w/in a string. So if the String is 1122334455 I would need to update just '1122' and replace with '0000' - leaving '0000224455'.

I've tried the update/replace functions and they're not working. How would I do this or would I just create a trigger?

Thanks in advance any info

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-10 : 14:36:00

Well update with replace certainly will work, so please show us what you tried and perhaps what error you got or what it actually did.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

MutantNinjaLoungeSinger
Starting Member

14 Posts

Posted - 2007-04-10 : 14:36:49

What part are you having trouble with?

The string output (1122334455 --> 0000224455)?

How frequently must this update occur?
Every record? After some process?

jsnyder
Starting Member

3 Posts

Posted - 2007-04-10 : 14:49:16

Basically what I'm trying to do is ONLY replace the first 4 characters in every instance so that I don't have to do thousands of updates - so where there's a 1122 at the beginning of a string it becomes 0000... This would be a one time update.

How would you all tackle this? Thanks so much for the quick reply!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-10 : 14:57:11

Here's one way:

UPDATE YourTable
SET YourColumn = REPLACE(YourColumn, '1122', '0000')
WHERE YourColumn = '1122' + SUBSTRING(YourColumn, 5, DATALENGTH(YourColumn))

And here's another:

UPDATE YourTable
SET YourColumn = REPLACE(YourColumn, '1122', '0000')
WHERE YourColumn = '1122' + RIGHT(YourColumn, DATALENGTH(YourColumn) - 4)

Tara Kizer
http://weblogs.sqlteam.com/tarad/

MutantNinjaLoungeSinger
Starting Member

14 Posts

Posted - 2007-04-10 : 15:33:25

UPDATE theTable
SET theColumn = '0000' + SUBSTRING(theColumn, 5, DATALENGTH(theColumn))

You'd have to set your WHERE filter if you want to focus on a particular set of records.

TKizer's method works too, unless "1122" happens to also be in the middle or end of the number string. (11223451122 would become 00003450000)
If you can verify your data doesn't have any occurrences that might create that scenario, her method is probably a little clearer to understand.

jsnyder
Starting Member

3 Posts

Posted - 2007-04-10 : 16:31:58

I guess the 'DATALENGTH' function doesn't work in MySQL - uggh. Anyway thanks for all the help

MutantNinjaLoungeSinger
Starting Member

14 Posts

Posted - 2007-04-10 : 18:22:31

In MySql, the synonym for DATALENGTH is LENGTH

Asharudeen
Starting Member

1 Post

Posted - 2008-01-08 : 07:43:49

Hi,

I need to replace a specific portion of string in a database with another database. For example, see the following strings

'Rajkumar', 'RajMohan'

Here I would like to replace the term 'Raj' with the string 'Rahul'. ie, like 'RahulKumar', 'RahulMohan'.

Important: I am using SQLite3 (www.sqlite.org) database.

Since, sqlite doesn't support 'Replace functions, Could any one help me regarding this.

niveditha
Starting Member

1 Post

Posted - 2008-09-04 : 02:10:02

Hi,

can you please tell me that what function can be used in MySql instead of DataLength() as the one which we have in MsSql.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 02:20:54

quote:
Originally posted by niveditha

Hi,

can you please tell me that what function can be used in MySql instead of DataLength() as the one which we have in MsSql.

you would need to post in some MySQL forums to get solution for MySQL specific questions. this is MSSQL forum.

rdemartini
Starting Member

2 Posts

Posted - 2009-04-16 : 11:18:15

quote:
Originally posted by Asharudeen

Hi,

I need to replace a specific portion of string in a database with another database. For example, see the following strings

'Rajkumar', 'RajMohan'

Here I would like to replace the term 'Raj' with the string 'Rahul'. ie, like 'RahulKumar', 'RahulMohan'.

Important: I am using SQLite3 (www.sqlite.org) database.

Since, sqlite doesn't support 'Replace functions, Could any one help me regarding this.

I noticed that you are using SQLite3. Do you know how to convert an IPhone timeIntervalSince1970 stored as a REAL to .NET dateTime

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources