CODEWITHSUNDEEP
sqlsql-server
Rohini Mathur
Rohini Mathur

Reputation: 441

Remove special character from begining and end of string in sql

sql server 2008

I have a data in a column something like

"Brake pad kit, disc brake"
/Brake disk (sold separately).
"The belt pulley, crankshaft"
 Fuel Pump

the special character are "",space,/

i want to remove any special character or space present in begining or end of the string.

is this possible to do in sql, not sure. Please share your thoughts.

Upvotes: 0

Views: 829

Answers (1)

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

Reputation: 93694

Here is one way to do it using String functions

DECLARE @str VARCHAR(200)= '"The belt pulley, crankshaft"' 

SELECT Reverse(CASE 
                 WHEN LEFT(Reverse(scd_str), 1) LIKE '[A-Z]' OR LEFT(Reverse(scd_str), 1) LIKE '[a-z]' THEN Reverse(scd_str) 
                 ELSE Substring(Reverse(scd_str), 2, Len(Reverse(scd_str))) 
               END) 
FROM   (SELECT CASE 
                 WHEN LEFT(string, 1) LIKE '[A-Z]' OR LEFT(string, 1) LIKE '[a-z]' THEN string 
                 ELSE Substring(string, 2, Len(string)) 
               END AS Scd_Str 
        FROM   (SELECT Rtrim(Ltrim(@str)) AS string) A) B

Result : The belt pulley, crankshaft

Upvotes: 2

Related Questions