CODEWITHSUNDEEP
mysqlsqlmysql-workbench
Abhi
Abhi

Reputation: 92

SQL to remove Zero's in the string in MYSQL

SQL to remove Zero's in the string for a particular column. I am tried using the REPLACE but it is completing removing all the 0's

EXAMPLE: "(REPLACE(Column,'0','')" But that didn't solved my problem. It removed all the Zero's which is not the requirement.

Can some one help me in writing the SQL statement to remove only 1st zero from the string.

Column

00-01-09

07-00-02

03-04-00

03-04-06

Result

0-1-9

7-0-2

3-4-0

3-4-6

Thank you

Upvotes: 0

Views: 2796

Answers (4)

webdad3
webdad3

Reputation: 9080

How about this?

select concat(trim(leading 0 from substr(col1,2,2)) , 
              trim(leading 0 from substr(col1,5,2)) , 
              substr(col1,8,1))
from t;

Here is the fiddle I was working with

Results:

1-1-1 
3-4-0

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Try this:

select substring(REPLACE(concat('-', Column), '-0', '-'), 2)

This time, there is a SQL Fiddle showing it working.

Upvotes: 2

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

select substr(c,2,2) || substr(c,5,2) || substr(c,8,1) 
from t;

Upvotes: 0

jcgoodrich
jcgoodrich

Reputation: 11

This isn't very elegant, but should work if it's just a "one time" thing:

Step 1:

Create a temporary table with six fields. 3 for original numbers and 3 for cleaned numbers.

Step 2: parse out and insert your original values into temp table first 3 fields:

insert into temp_table(first_number,second_number,third_number) select left(field,2) as first_number, substring(field,3,2) as second_number, substring(field(6,2) as third_number from original_table

Step 2: "clean" each number with the following statement:

update temp_table set first_number_cleaned=substring(first_number,2,1) where left(first_number,1)=0;

update temp_table set first_number_cleaned=first_number where left(first_number,1)<>0

repeat for each number

Step 4: update original table or create new one however you need to join it:

select concat(first_number_cleaned,'-',second_number_cleaned,'-',third_number_cleaned) as cleaned_string from temp_table

Upvotes: 0

Related Questions