CODEWITHSUNDEEP
sqlsql-server
Gopal
Gopal

Reputation: 11972

How to remove a specific character from the string

Using SQL Server 2008

String like: 'C/123232323' or '/343434343443' or 'C2323232322'

From the string i want to remove C and /

Tried Query

Select replace ('/1233434', 'C/', '')

The above query is working if C/ both is there. if / only there then the replace is not working. if C only there then the replace is not working. How to achieve for both condition

Expected output 

123232323
343434343443
2323232322

Need Query output

Upvotes: 0

Views: 67

Answers (3)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You could use two nested REPLACE:

WITH SampleData(string) AS(
    SELECT 'C/123232323' UNION ALL 
    SELECT '/343434343443' UNION ALL 
    SELECT 'C2323232322'
)
SELECT REPLACE(REPLACE(string,'C',''),'/','')
FROM SampleData

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

Use Patindex + Substring

DECLARE @str VARCHAR(50)='/343434343443' ---or '/343434343443' or 'C2323232322'
SELECT Substring(@str, Patindex('%[0-9]%', @str), Len(@str))

Upvotes: 0

JanR
JanR

Reputation: 6132

You can achieve this by nesting replace() like so:

select replace(replace('C/12341234','/',''),'C','')

Probably not the prettiest but it works :)

Upvotes: 2

Related Questions