CODEWITHSUNDEEP
sqlsql-server
Ranjay Kumar
Ranjay Kumar

Reputation: 61

How to remove the begining and ending characters if those are '0' in SQL Server

I currently have a table named DATA it has entries like the following:

abc000
ab000cde
000abc

I just want to remove all 0 from beginning and the end. If 0 comes in between the character then it will remain same.

Upvotes: 3

Views: 331

Answers (4)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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 substring from first nonzero symbol till first nonzero symbol in reversed string.

Upvotes: 2

shA.t
shA.t

Reputation: 16958

I use this trick:

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

Upvotes: 2

A l w a y s S u n n y
A l w a y s S u n n y

Reputation: 38502

This may works for SQL, as this removes leading & trailing "000" from your string.

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

See more

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

Upvotes: 0

BICube
BICube

Reputation: 4681

Say your data exists in column called Col1, then this expression should do it

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

Upvotes: 2

Related Questions