How to replace a character in a string using T-SQL

Question

In my table column, i have below sample data

Test1 145,  Area 1
Test2 146,
Test3 145,  Area 2, Plot 10

What i want to achieve is to replace "," in the string but only if it is the last character. If i have more characters after "," then the replace should leave the string as it is.

In the example above, the replace would only work in line 2.

The expected out put would be like below

Test1 145,  Area 1
Test2 146 
Test3 145,  Area 2, Plot 10

In line 2 above, "," has been replaced with empty space.

I have tried this Replace(column1, ', ', '') AS ColName but this replaces all the "," in Test1 and Test3.

So the 3 lines are a single value or are you showing 3 rows? — Alex K.
– Alex K., Commented Jun 9, 2016 at 13:07

gotqn · Accepted Answer · 2016-06-09 13:09:13Z

2

You can try this:

DECLARE @value VARCHAR(1024) = 'Test2 146,';

SELECT IIF(RIGHT(@value,1) = ',', LEFT(@value, LEN(@value) - 1), @value);

For column it looks like below:

DECLARE @DataSource TABLE
(
    [value] VARCHAR(1024)
);

INSERT INTO @DataSource ([value])
VALUES ('Test1 145,  Area 1')
      ,('Test2 146,')
      ,('Test3 145,  Area 2, Plot 10');


SELECT IIF(RIGHT([value],1) = ',', LEFT([value], LEN([value]) - 1), [value])
FROM @DataSource;

answered Jun 9, 2016 at 13:09

gotqn

43.9k47 gold badges167 silver badges256 bronze badges

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

2 Comments

StackTrace Over a year ago

looks like IIF is only available in 2012+

gotqn Over a year ago

You can use CASE statement - check the answers below.

sagi · Accepted Answer · 2016-06-09 13:18:56Z

2

You can also do this will LIKE and IIF :

SELECT IIF(t.Column LIKE '%,' , LEFT(t.column, LEN(t.column) - 1) , t.column) as new_val
FROM YourTable t

For older versions: You can use CASE EXPRESSION since IIF is only available since 2012+ version(Link by @gotqn)

SELECT CASE WHEN t.Column LIKE '%,' 
            THEN LEFT(t.column, LEN(t.column) - 1) 
            ELSE t.column
       END as new_val
FROM YourTable t

edited Jun 9, 2016 at 13:18

answered Jun 9, 2016 at 13:12

sagi

40.6k7 gold badges66 silver badges87 bronze badges

9 Comments

Sean Lange Over a year ago

Of course I also just noticed this is tagged 2005 so that won't work for the OP. But it will work for others that stumble in here in the future.

sagi Over a year ago

The IIF won't work, but the case will won't it? Or another function here is not available in that version either?

gotqn Over a year ago

CASE is OK - IIF is from 2012+ editions (msdn.microsoft.com/en-us/library/hh213574.aspx)

Sean Lange Over a year ago

Yes sorry my comment was not very clear. The case expression should work fine. The IIF version should work too but is not likely going to work for the OP as they are on 2005.

StackTrace Over a year ago

I just tested the case expression but still didn't work, what i have found to be working is the answer by @nscheaffer.

|

Isaac · Accepted Answer · 2016-06-09 13:12:43Z

1

I am pretty sure IIF isn't available in SQL Server 2005. This is basically the same logic as the previous answer using CASE instead.

declare @MyString varchar(50)

set @MyString = 'Test2 146,'

select 
case
    when right(rtrim(@MyString), 1) = ',' then
        substring(@MyString, 1, len(rtrim(@MyString)) - 1)
    else
        @MyString
end

answered Jun 9, 2016 at 13:12

Isaac

3,3832 gold badges26 silver badges33 bronze badges

Comments

Giorgos Betsos · Accepted Answer · 2016-06-09 13:12:35Z

0

Something like this:

SELECT CASE 
          WHEN Column1 LIKE '%,' THEN STUFF(column1, LEN(column1), 1, '') 
          ELSE Column1
       END

answered Jun 9, 2016 at 13:12

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Comments

Usman Maqbool · Accepted Answer · 2016-06-09 13:48:29Z

0

This shows one way to do it.

DECLARE @test VARCHAR(30);

SET @test = 'Test1, 145, Area 1';

SELECT @test;

IF CHARINDEX(',', @test, LEN(RTRIM(@test))) > 0
BEGIN
    SET @test = Replace(@test, ',', '');
END

SELECT @test;

SET @test = 'Test2 146,';

SELECT @test;

IF CHARINDEX(',', @test, LEN(RTRIM(@test))) > 0
BEGIN
    SET @test = Replace(@test, ',', '');
END

SELECT @test;

SET @test = 'Test3 145, Area 2, Plot 10';

SELECT @test;

IF CHARINDEX(',', @test, LEN(RTRIM(@test))) > 0
BEGIN
    SET @test = Replace(@test, ',', '');
END

SELECT @test;

-- How to work into a SELECT statement

SET @test = 'Test2 146,';

SELECT CASE WHEN CHARINDEX(',', @test, LEN(RTRIM(@test))) > 0 THEN SUBSTRING(@test, 1, LEN(@test) - 1) ELSE @test END AS 'Converted Value';

edited Jun 9, 2016 at 13:48

Usman Maqbool

3,37111 gold badges34 silver badges50 bronze badges

answered Jun 9, 2016 at 13:20

NinePlanFailed

4055 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to replace a character in a string using T-SQL

5 Answers 5

2 Comments

9 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

9 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related