1

I want to update a database table field using another field in the same table. Currently I have this table called sources. 

Name    Code

In the name column I have values like this example :

  1. ' Deals On Wheels '
  2. 'Homesru - Abu Dhabi - Madinat Zayed Gold Centre'

And I am having this update statement : 

UPDATE Sources
SET    Code = REPLACE((LTRIM(RTRIM(Name))),' ','-')

the result is : 

Deals-On-Wheels-Al-Aweer

which is fine.

but for second one I have this :

Homesru---Abu-Dhabi---Madinat-Zayed-Gold-Centre

I want it to be like this : 

Homesru-Abu-Dhabi-Madinat-Zayed-Gold-Centre

How can I Achieve this ? Any Help is appreciated.

gvee
  • 16,732
  • 35
  • 50
user123456
  • 2,524
  • 7
  • 30
  • 57
  • Can you have this kind of string: `Homesru------Abu-Dhabi-------Madinat-------Zayed--------Gold----Centre`? Nevertheless, avoid this kind of string manipulation using tSQL. tSQL was is intended to recover information from database not to format it. Indeed you can do it, but better avoid it, save your database server cpu clocks for getting information in and out. – Yaroslav Nov 11 '13 at 13:56
  • @Yaroslav Actually I might have this kind of data because it is coming from user input. But if he has something like this , then that his choice.Thank you for your explanation. – user123456 Nov 11 '13 at 13:59
  • And then why you don't clean the data before making sending it to sql? It will be easier and faster. And you will not need to do any string cleaning/formatting on tsql. Just an advice, you question was answered so you are done. But next time you have a similar situation you will have another possible option. – Yaroslav Nov 11 '13 at 14:06
  • @Yaroslav sorry I did not get your point. Can you elaborate more ? – user123456 Nov 11 '13 at 14:09
  • You say you get the data from a user input. Then why you don't clean (remove unneeded characters for example) your string on your application (it could be java, javascript, c#, vb.net, whatever language you are using on your app) and after that then make your call to sql passing the cleaned string. This way the sql engine will do what it was meant to do, run a query and update your data. – Yaroslav Nov 11 '13 at 14:16
  • ActuallyI am just ruining this SQL for the first time because the Code is a newly added field to an existing table. But next time the user will provide the values and I will make sure that the data are in the valid format that I want. Thank you very much for your time. – user123456 Nov 11 '13 at 14:20

3 Answers3

3

As suggested by @DanielE. my answer will point to a more global solution, in case you ever need to replace duplicated/triplicated/quadriplicated/... occurrences of a character on a string.

I'll not create a full solution for this issue, is a recurring question and there are really good solutions around already. Check these links:

SQL Server Central: remove spaces between specific character in a string?. This forum post will point to the next link I'm posting here. But is good to know what they are asking and answering.

Replace multiple spaces with new one but you can slightly modify it to replace any character you want.

You can also rely on this answer Find and remove repeated strings from Aaron Bertrand.

Community
  • 1
  • 1
Yaroslav
  • 6,476
  • 10
  • 48
  • 89
1

try

REPLACE((LTRIM(RTRIM(REPLACE((LTRIM(RTRIM(Name))),' - ','-')))),' ','-')

this will first replace ' - ' with just '-'

gvee
  • 16,732
  • 35
  • 50
Daniel E.
  • 2,029
  • 3
  • 22
  • 28
  • And what if he has `Homesru------Abu-Dhabi-------Madinat-------Zayed--------Gold----Centre` – Yaroslav Nov 11 '13 at 13:53
  • then his data is wrong to start with, but in your specific example absolutely nothing would happen as he has no spaces. If you mean what if he has 'DoubleSpace-DoubleSpace' then he can add a level of 'DoubleSpace' to '' if he wants to... (the formatting strips double spaces) – Daniel E. Nov 11 '13 at 13:56
  • Ok, it works for this particular case, but...what if the string changes? Then, each time he receives a string he should check first how many `-` it has and rewrite the query. – Yaroslav Nov 11 '13 at 13:57
  • I agree with you, and your comment on the question, but he asked something specific, if you want to add an answer for the general rule I will gladly upvote it. This solved his need... – Daniel E. Nov 11 '13 at 14:00
  • I am adding the code field as new one, so I will fill it with the name values for the first time. But later on the the user will provide the value so I need the SQL for one time only. – user123456 Nov 11 '13 at 14:03
  • If @DanielE. answer solved your issue, remember to check it as the correct answer. – Yaroslav Nov 11 '13 at 14:28
0

You might want to look into using a UDF to do a regular expression search and replace. See https://launchpad.net/mysql-udf-regexp

mti2935
  • 11,465
  • 3
  • 29
  • 33