Change the last 2 characters of a string value in SQL

Question

0.00/5 (No votes)

See more:

How would I update the last two numbers in SQL.

Ex:
249691268200209200209

into:
249691268200209200231

What I have tried:

I just know how to select the data that ends with the tail end of those number I do not understand how to update the number.

SELECT data from data_table where data = '%0209';

I'm assuming:
UPDATE data_table set data = '%0231' WHERE data = '%0209';

Posted 3-Mar-20 8:32am

Member 14762163

Updated 26-Mar-20 22:45pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ishan_shah · Answer 1 · 2020-03-26T22:47:00

Solution 2

We can solve it using the SUBSTRING Like the following update query :

UPDATE data_table
SET data = SUBSTRING(data, 1, LEN(data) - 2) + '31';

We can also permorm this query using replace method with Substring

UPDATE data_table
SET data = REPLACE(data, SUBSTRING(data, (LEN(data)-2),(LEN(data)+2)),'31')

Posted 26-Mar-20 22:47pm

ishan_shah

Comments

Richard Deeming 27-Mar-20 8:46am

Your first option is precisely what I posted in the comments three weeks ago.

Your second option is just wrong for a couple of reasons.
- The third argument to substring is the number of characters to return, not the index of the last character to return.
- If the last two digits appear anywhere else in the string, all instances will be replaced.

If you look at the example given in the question:
249691268200209200209 ⇒ 249691268200209200231
Your second example would incorrectly change the value to:
2496912682003120031

Richard Deeming · Answer 2 · 2020-03-03T09:09:00

A couple of options spring to mind:

SQL

UPDATE
    data_table
SET
    data = STUFF(data, Len(data) - 1, 2, '31')
WHERE
    data Like '%0209'
;

Or:

SQL

UPDATE
    data_table
SET
    data = Left(data, Len(data) - 2) + '31'
WHERE
    data Like '%0209'
;

NB: You need to use Like to match the pattern; otherwise, you'll only update records where the value is exactly equal to the string '%0209'.

STUFF (Transact-SQL) - SQL Server | Microsoft Docs[^]
LEFT (Transact-SQL) - SQL Server | Microsoft Docs[^]
LIKE (Transact-SQL) - SQL Server | Microsoft Docs[^]