Menu
I've been using this for some time:
SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col)) However recently, I've found a problem with columns with all "0" characters like '00000000' because it never finds a non-"0" character to match.
An alternative technique I've seen is to use TRIM:
REPLACE(LTRIM(REPLACE(str_col, '0', ' ')), ' ', '0') This has a problem if there are embedded spaces, because they will be turned into "0"s when the spaces are turned back into "0"s.
I'm trying to avoid a scalar UDF. I've found a lot of performance problems with UDFs in SQL Server 2005.
874
SELECT CEIL((AVG(salary)) - (AVG(REPLACE(salary, '0', '')))) AS avg_salary FROM employees; REPLACE() : used to remove 0 from salary. AVG() : used to calculate average salary.
SQL Server TRIM() Function The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string. Note: Also look at the LTRIM() and RTRIM() functions.
SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col)) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
