1

I have varchar column, its name is column1 and has records like 111232435-0 and 1123443530. The last digit is not always 0; it can be 0-9.

Some of them are digits with (-), other are digits. I want to change digits to digits with(-).

For example: 1123443530 -> change to -> 112344353-0

Pseudo-code:

if record just be digit just update in column3
else
the last digit separate by (-)
and then record update in column3

In SQL Server 2012 and put this new record and records with (-) in new column.

What should I do?

4

Another alternative is to use STUFF function.

IF OBJECT_ID('tempdb..#ExampleValues') IS NOT NULL
    DROP TABLE #ExampleValues

CREATE TABLE #ExampleValues (
    ExampleValue VARCHAR(100), 
    UpdatedValue VARCHAR(100))

INSERT INTO #ExampleValues (ExampleValue)
VALUES
    ('112344353-0'),
    ('11443-8'),
    ('112344-9'),
    ('11234435348'),
    ('11234448712')

UPDATE T SET
    UpdatedValue = CASE 
        WHEN T.ExampleValue NOT LIKE '%-%'  -- If the literal does not contain a hyphen
            THEN STUFF(
                T.ExampleValue,             -- Put in the literal
                LEN(T.ExampleValue),        -- Just before the last character
                0,                          -- While replacing 0 characters ("stuffing" in between)
                '-')                        -- Place a hypen!
        ELSE
            T.ExampleValue END
FROM
    #ExampleValues AS T

SELECT
    T.ExampleValue,
    T.UpdatedValue
FROM
    #ExampleValues AS T

Result:

ExampleValue    UpdatedValue
112344353-0     112344353-0
11443-8         11443-8
112344-9        112344-9
11234435348     1123443534-8
11234448712     1123444871-2
1

Is this something you are looking for

DECLARE @table TABLE(varname      VARCHAR(255),
                     newvarname   VARCHAR(255)
                    ); 

INSERT INTO @table
SELECT '111232435-0',NULL
UNION ALL
SELECT '1123443530',NULL;

UPDATE @table
   SET 
       newvarname = CASE
                       WHEN varname NOT LIKE '%-%' THEN 
                       LEFT(varname,LEN(varname)-1)+'-'+RIGHT(varname,1)
                       ELSE varname
                    END;


SELECT *
  FROM @table;

based on the above logic you can create the update statement.

Edit 1 Updated the code if the last digit can be any number and assuming that we need to only change the column which don't have a seperator -

  • Probably more efficient to use: UPDATE table1 SET column1 = LEFT(column1,LEN(column1)-1)+'-'+RIGHT(column1,1) WHERE column1 LIKE '%-%' and – Lennart Aug 21 '18 at 7:18
  • @Lennart, yup that's right. I moved the LIKE to CASE since I think that's a new column and not on the same column. The OP says its a new column – Biju jose Aug 21 '18 at 7:22
  • Ah, I did not see that. I only read that he/she wanted to change the column – Lennart Aug 21 '18 at 8:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.