CODEWITHSUNDEEP
sql-server
ANR
ANR

Reputation: 167

Remove first and last character if has specific character in SQL Server?

Please help me to remove the first and last character if it has specific character (#) in the string otherwise do not remove?

Input 1: #Test#SQL#

Desired Output : Test#SQL

Input 2: Test#SQL

Desired Output : Test#SQL

Thank you in advance

Upvotes: 1

Views: 3593

Answers (3)

Ullas
Ullas

Reputation: 11566

Just an another perspective by using a CASE expression and a combination of LEFT and RIGHT string functions.

Query

select case when left(@str, 1) = '#' and right(@str, 1) = '#'
then left((right(@str, len(@str) - 1)), len((right(@str, len(@str) - 1))) - 1)
when left(@str, 1) = '#' and right(@str, 1) <> '#'
then right(@str, len(@str) - 1)
when left(@str, 1) <> '#' and right(@str, 1) = '#'
then left(@str, len(@str) - 1)
else @str end as str;

Find a demo here

Upvotes: 2

John Pasquet
John Pasquet

Reputation: 1842

This may not be the most efficient way, but you could put it in a function easily enough:

Essentially, you test the first character in the string and remove it if it's the # sign. Then you do the same with the last character.

DECLARE @A VARCHAR(25)
SET @A = '#Test#SQL#'

SET @A = CASE
    WHEN SUBSTRING(@A, 1, 1) = '#'
        THEN SUBSTRING(@A, 2, LEN(@A))
        ELSE @A
    END

SET @A = CASE
    WHEN SUBSTRING(@A, LEN(@A) , 1) = '#'
        THEN SUBSTRING(@A, 1, LEN(@A) - 1)
        ELSE @A
    END

SELECT @A

Upvotes: 1

Koby Douek
Koby Douek

Reputation: 16693

You can use RIGHT and LEFT like this:

Update myTable set myField = RIGHT(myField,LEN(myField)-1) WHERE myField LIKE '#%';
Update myTable set myField = LEFT(myField,LEN(myField)-1) WHERE myField LIKE '%#';

This will ensure that even if the value starts or ends with the # symbol, it will be removed.

Upvotes: 2

Related Questions