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

PRA

SOLVED CLOSED ACTIVE ANSWERED
User: PRA
Posted: on Dec 07, 2018 12:07 AM
Forum: SQL Server
Answer: 1
Views: 1782

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

Download FREE API for Word, Excel and PDF in ASP.Net

pandeyis...

0 2

Reply
ANSWER
User: pandeyism
Replied: on Dec 07, 2018 03:18 AM
Report

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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

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

Company

Explore

Follow us

Contact Us