Replace specific character from First and Last position of column value in SQL Server

Last Reply 3 months ago By pandeyism

Posted 3 months ago

Hi! I want remove define latter from right or left by length. For example:

DECLARE @INFO AS TABLE (Id INT, NAME VARCHAR(15))
INSERT INTO @INFO VALUES (1,'Orange')
INSERT INTO @INFO VALUES (2,'Onion')
INSERT INTO @INFO VALUES (3,'Potatoes')
INSERT INTO @INFO VALUES (4,'Melon')
select REPLACE(name,left('o',1),'') from @INFO

output result:

Id

NAME

1

range

2

nion

3

Potatoes

4

Melon

Posted 3 months ago

Hey PRA,

Please refer below query.

SQL

DECLARE @INFO AS TABLE (Id INT, NAME VARCHAR(15))
INSERT INTO @INFO VALUES (1,'Orange')
INSERT INTO @INFO VALUES (2,'Onion')
INSERT INTO @INFO VALUES (3,'Potatoes')
INSERT INTO @INFO VALUES (4,'Melon')

SELECT Id,CASE
    WHEN name LIKE 'o%' THEN SUBSTRING(name, 2, LEN(name)-1)
    WHEN name LIKE '%o' THEN LEFT(name, LEN(name)-1)
    ELSE name 	
END Name
FROM @INFO

Output

Id Name
1 range
2 nion
3 Potatoes
4 Melon