Home Trimming any Leading or trailing characters
Reply: 3

Trimming any Leading or trailing characters

Rameshwar Pawale
1#
Rameshwar Pawale Published in 2018-02-13 11:08:45Z

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

Shnugo
2#
Shnugo Reply to 2018-02-13 11:15:46Z

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...

Jayasurya Satheesh
3#
Jayasurya Satheesh Reply to 2018-02-13 11:18:10Z

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
Paparazzi
4#
Paparazzi Reply to 2018-02-13 12:26:29Z

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

Trim

TRIM ( [ characters FROM ] string )

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.440979 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO