Link to home
Start Free TrialLog in
Avatar of kishan66
kishan66Flag for United States of America

asked on

Elimate special char(s) from start & end of a string, SQL

how to eliminate starting comma(s) and ending comma(s) in a string using Select, SQL SERVER 2008R2

Example scenarios to handle
1) @string = ',,2017 Q1,2018 Q2,'
2) @string ='2017 Q3,,,'
3) @string = ',,2017 Q2'

Basically looking for the shortest syntax

Environment SQL SERVER 2008R2
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Scott - Last string has some issues --pls chk..
string             new_string
------------------ ------------------
,,2017 Q1,2018 Q2, 2017 Q1,2018 Q2
2017 Q3,,,         2017 Q3
,,2017 Q2          2017 Q2
,,,,2017,,Q4,,,//  2017,,Q4

Mine also failed.. :)
How does that have "issues"?  Code does exactly as requested: removes leading and trailing "special char(s)".

Code might be slightly more consistent with same naming style for leading and trailing chars:

SELECT string,
    SUBSTRING(string, leading_bad_char_count + 1, LEN(string) - leading_bad_char_count - trailing_bad_char_count) AS new_string
FROM ( VALUES(',,2017 Q1,2018 Q2,'),('2017 Q3,,,'),(',,2017 Q2'),(',,,,2017,,Q4,,,//'),('2017 q1')) AS strings(string)
CROSS APPLY (
    SELECT PATINDEX('%[0-9A-Z]%', string) - 1 AS leading_bad_char_count,
        PATINDEX('%[0-9A-Z]%', REVERSE(string)) - 1 AS trailing_bad_char_count
) AS assign_alias_names
Ohhh yes correct .. mine is also fine. my bad.!
Aside from considering only commas as 'special char(s)' -- which does seem OK as the OP did specify only commas in the main body of the q --  your code will corrupt the string if it contains a '|'.  You should probably use CHAR(7) or some other char much more certain to never appear in the data.
Avatar of kishan66

ASKER

thanks for the quick response.