kishan66
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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,,,//')
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.
ASKER
thanks for the quick response.
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.. :)