CODEWITHSUNDEEP
sqlsql-server-2008
Manish Sharma
Manish Sharma

Reputation: 2426

How to replace first and last character of column in sql server?

I have a database column and its give a string like ,Recovery, Pump Exchange,.

I want remove first and last comma from string.

Expected Result : Recovery, Pump Exchange.

Upvotes: 16

Views: 80733

Answers (9)

Karthikeyan P
Karthikeyan P

Reputation: 1267

You can try this way using SUBSTRING to replace first and last character of column in table.

In this example, I have used double quotes(") instead of comma(,). It will replace only first and last character(“), if the column value starts with double quotes(“) and ended with double quotes(“).

DECLARE @tblProducts TABLE (ProductName VARCHAR(100))

INSERT INTO @tblProducts VALUES ('"Recovery 10x12" Pump Exchange"')
INSERT INTO @tblProducts VALUES ('"Recovery Pump Exchange 10x12""')
INSERT INTO @tblProducts VALUES ('Recovery Pump Exchange 10x12"')

--REPLACE Last Char(")
UPDATE @tblProducts SET ProductName = SUBSTRING(ProductName, 1, len(ProductName) - 1) FROM @tblProducts WHERE ProductName LIKE '"%' AND ProductName LIKE '%"'

--REPLACE First Char(")
UPDATE @tblProducts SET ProductName = SUBSTRING(ProductName, 2, len(ProductName) - 1) FROM @tblProducts WHERE ProductName LIKE '"%'

SELECT * FROM @tblProducts

Result:

enter image description here

Upvotes: 0

Amélie Dupré
Amélie Dupré

Reputation: 78

This is an old post but I've decided to add my solution as it will remove the first and last commas of a comma separated string without removing the other commas and also will work with strings that don't start with a comma:

DECLARE @words VARCHAR(50) = ',Recovery, Pump Exchange,'
SELECT REPLACE(SUBSTRING(@words , 1, 1),',','') + SUBSTRING(@words, 2, LEN(@words)-2) + REPLACE(SUBSTRING(@words, LEN(@words), 1), ',', '')

Upvotes: 0

Ragul
Ragul

Reputation: 512

For removing same character you can use below system function TRIM

Only from 2017

DECLARE @str VARCHAR(MAX) = '[REMOVE THIS CLOSE BRACKET]'

SELECT TRIM('[]' FROM @str)

But for different character removing you need to use SUBSTRING

SELECT SUBSTRING(@str,2,LEN(@str)-2)

Upvotes: 3

TaBi
TaBi

Reputation: 21

SELECT LEFT(RIGHT(',Recovery, Pump Exchange,',LEN(',Recovery, Pump Exchange,')-1),LEN(',Recovery, Pump Exchange,')-2)

Upvotes: 0

gbn
gbn

Reputation: 432180

Using LEN could backfire because LEN ignores trailing spaces. These could be added because of ANSI_PADDING defaulting to ON. So, you'd need RTRIM.

For completeness, I've added LTRIM too...

REVERSE(SUBSTRING(REVERSE(RTRIM(LTRIM(SUBSTRING(MyCol, 2, 8000)))), 2, 8000))

Upvotes: 3

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

You can use SUBSTRING for that:

SELECT
    SUBSTRING(col, 2, LEN(col)-2)
FROM ...

Obviously, an even better approach would be not to put leading and trailing commas there in the first place, if this is an option.

I want to remove last and first comma only if exist otherwise not.

The expression becomes a little more complex, but the idea remains the same:

SELECT SUBSTRING(
    col
,  CASE LEFT(@col,1) WHEN ',' THEN 2 ELSE 1 END
,  LEN(@col) -- Start with the full length
             -- Subtract 1 for comma on the left
      - CASE LEFT(@col,1) WHEN ',' THEN 1 ELSE 0 END 
             -- Subtract 1 for comma on the right
      - CASE RIGHT(@col,1) WHEN ',' THEN 1 ELSE 0 END
)
FROM ...

Upvotes: 30

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

Try like this:

if(Substring(@mykeyword, 1,1) = ',' and Substring(@mykeyword, LEN(@mykeyword) - 1, LEN(@mykeyword))=',')
SET @mykeyword = Substring(@mykeyword, 2, LEN(@mykeyword) - 2)

Upvotes: 0

bendataclear
bendataclear

Reputation: 3850

Alternatively to dasblinkenlight's method you could use replace:

DECLARE @words VARCHAR(50) = ',Recovery, Pump Exchange,'
SELECT REPLACE(','+ @words + ',',',,','')

Upvotes: 4

Raging Bull
Raging Bull

Reputation: 18737

Use Substring():

SET @String=SUBSTRING(@String ,2,Len(@String)-2)

SUBSTRING() returns part of an expression.

Syntax:

SUBSTRING ( expression ,start , length )

Upvotes: 2

Related Questions