Remove trailing zeros from each SQL Server record

Question:

Good afternoon, I have the following records:

1201045000000
1201045310000
1201000140000
1201045340000
1201045453400
1200000545455

I want to make a script that gives me the following results:

1201045
120104531
120100014
120104534
12010454534
1200000545455

As you can see, the zeros that are at the end of the string are removed, keep in mind:

  1. The number of trailing zeros varies.
  2. There are records that have zeros in the middle of the string, ahem: 12 00000 545455, 1201 000 140 000

I have not been able to use substring because of the different amounts of trailing zeros and because it has zeros in the middle of the string.

I hope you can help me. thank you.

Answer:

You could do it by combining the REVERSE and the CONVERT in SQL SERVER

Example:

as string

 select REVERSE(convert(varchar(20),convert(BIGINT ,REVERSE('1201045000000'))))

as a numeric value

select REVERSE(convert(varchar(20),convert(BIGINT ,REVERSE(1201045310000))))
Scroll to Top