Change string with sql UPDATE query

Question

Question

How could I change the following code from SGC1-0001[ to SGC1-001.

The table is stations with column code. I need to use an update query, but not sure how to use it.

There are more records in the column that differ slightly but I should be able to work it out with an example to do one.

Sample Data

I would like to do them in chunks though not one at a time.

SGC1-0001[
SGC1-0002[
..
SGC1-0019[
SGC1-0021[
SGC2-0001[
SGC2-0002[
..
SGC2-0016[
SGC2-0017[
SGC3-0003[
SGC3-0004[
...
SGC4-0018[
SGC4-0021[
SGC4-0022[
SGC4-0025[
SGC4-0029[

Logic (pseudo code)

Delete first 0 and last [ from all

Can you provide other starting and desired ending values? From what you've provided, we don't know if the solution you need must be dynamic or not. — Dan
– Dan, Commented Jan 7, 2015 at 16:40
Thanks for adding additional example starting values. What would the desired ending value for SGC4-0029[ be, for example? — Dan
– Dan, Commented Jan 7, 2015 at 16:47
Basically it will be string manipulation using SQL REPLACE and then whichever ones you want to do, use the WHERE clause to only do those. — logixologist
– logixologist, Commented Jan 7, 2015 at 17:02
@Dan Cundy:- see the my anwser, as it will delete the last '[' and first 0 after '-' from all records — HaveNoDisplayName
– HaveNoDisplayName, Commented Jan 7, 2015 at 17:03

Drew · Accepted Answer · 2015-01-07 18:41:04Z

3

If you're wanting to trim the last character of all records in that column as well as remove the leading 0, the following should do the trick:

UPDATE stations 
SET    code = REPLACE(LEFT(code, LEN(code) - 1), '-0', '-')

If you're just wanting to replace the leading 0, following should do:

UPDATE stations 
SET    code = REPLACE(code, '-0', '-')

Also, the following may be of interest:

String Functions

edited Jan 7, 2015 at 18:41

answered Jan 7, 2015 at 16:41

Drew

2,7056 gold badges37 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Drew Over a year ago

@DanCundy sure--revised!

Dan Cundy Over a year ago

And I have now removed the ] off them all so this query won't work any more. If you could explain it I could maybe edit it.

Drew Over a year ago

@DanCundy the REPLACE function 'Replaces all occurrences of a specified string value with another string value.' So in this case replace is replacing all occurances of '-0' with '-' in the string which was passe (in this case 'LEFT(code, LEN(code) - 1)', which is essentially the record without the trailing ']'. Hope this helps! msdn.microsoft.com/en-us/library/ms186862.aspx

Dan Cundy Over a year ago

That makes sense, I was getting confused thinking they were 'minus 0'. Thank you very much for your help.

Drew Over a year ago

Cool, and gotcha sir. Welcome!

HaveNoDisplayName · Accepted Answer · 2015-01-07 17:04:47Z

0

Try This, Update query

Because all your code have '[' at last then you can use this query. it will delete the last '[' and first 0 after '-' from all records

UPDATE stations
SET  CODE = LEFT(replace(code,right(code,1),''),4) + '-'+
            RIGHT(RIGHT(replace(code,right(code,1),''),4),3)

It will replace SGC1-0029[ to SGC1-029 AND SGC1-0001[ to SGC1-001

Better to refer some sql basic tutorials

edited Jan 7, 2015 at 17:04

answered Jan 7, 2015 at 16:39

HaveNoDisplayName

8,537106 gold badges41 silver badges50 bronze badges

Collectives™ on Stack Overflow

Change string with sql UPDATE query

Question

Sample Data

Logic (pseudo code)

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Question

Sample Data

Logic (pseudo code)

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related