CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
Rameshwar Pawale
Rameshwar Pawale

Reputation: 682

Trimming any Leading or trailing characters

I have a table where I have some raw data. My requirement is to trim any leading or trailing spaces and operator characters in the given string column. Examples of operator characters include + - . > < = : ;"

Examples:

Value +Payment should be trimmed to Payment
Value ->300 Write should be trimmed to 300 Write

Upvotes: 1

Views: 3538

Answers (3)

paparazzo
paparazzo

Reputation: 45096

What version are you on. Trim will do characters on 2017 version.

Trim

TRIM ( [ characters FROM ] string )

Upvotes: 1

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Try with PATINDEX

;WITH CTE
AS
(
    SELECT
       Val = '+Payment'
    UNION
    SELECT
       ' 300 Write'
)
SELECT
    *,
    NewVal = RTRIM(LTRIM(SUBSTRING(Val,PATINDEX('%[A-Z0-9]%',Val),LEN(Val))))
    FROM CTE

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

You can try this:

DECLARE @tbl TABLE(YourString VARCHAR(100));
INSERT INTO @tbl VALUES('+Payment'),('->300 Write'),('-:<Test,:%');

SELECT SUBSTRING(YourString,A.posFirst,A.posLast-A.posFirst+2)
FROM @tbl 
OUTER APPLY(SELECT PATINDEX('%[a-zA-Z0-9]%',YourString) AS posFirst
                  ,LEN(YourString)-PATINDEX('%[a-zA-Z0-9]%',REVERSE(YourString)) AS posLast) AS A

The result

Payment
300 Write
Test

You can add any allowed character to the pattern...

Upvotes: 4

Related Questions