Clever Geek Handbook

How to remove leading and trailing characters when they are "0" in SQL Server

I currently have a table named DATA

it has entries like:

abc000
ab000cde
000abc

I just want to remove all 0s from start to finish. If 0 is between a character, then it will remain the same.

+3


source to share


4 answers


This also works for leading and trailing zeros at the same time:

declare @s varchar(15) = '00abc00efg000'
select substring(@s, 
                 patindex('%[^0]%', @s), 
                 len(@s)-patindex('%[^0]%', reverse(@s))-patindex('%[^0]%', @s)+2);


Description: This is the substring from the first nonzero character to the first nonzero character in the inverted string.

+2


source


Say your data exists in a column Col1

then this expression should do this



select CASE 
           WHEN RIGHT(col1 , 1) = '0'
           THEN SUBSTRING(col1,0,PATINDEX('%[A-Z1-9]%',REVERSE(col1)))
           WHEN LEFT(col1 , 1) = '0'
           THEN SUBSTRING(col1,PATINDEX('%[A-Z1-9]%',col1),LEN(col1))
           ELSE 
            Col1 
        END AS 'ParsedCol1'
FROM Data
+2


source


I am using this trick:

SELECT 
    REPLACE(REPLACE(RTRIM(LTRIM(REPLACE(REPLACE(col1, ' ', CHAR(8)), '0', ' '))), ' ', '0'), CHAR(8), ' ')
FROM 
    yourTable
+2


source


This might work for SQL as it removes the leading and trailing "000" from your string.

SELECT TRIM(BOTH ‘000’ FROM    
‘your_data_table_column’);

More details

http://www.mydigitallife.info/remove-or-trim-first-or-last-few-characters-in-mysql-database-with-sql/

http://www.w3resource.com/mysql/string-functions/mysql-trim-function.php

0


source






More articles:


All Articles