Which is faster to remove leading spaces and zeros: Cast or Trim?

Database
Highlighted
Enthusiast

Which is faster to remove leading spaces and zeros: Cast or Trim?

Hello, i have a column (STORE_ID) that contains only numeric characters and in some cases leading spaces.  I want to trim leading zeros and spaces.  Which is faster? 

 

cast(cast(STORE_ID as integer) as Varchar(75))

 

trim (leading '0' from trim(leading from STORE_ID))

 

6 REPLIES
Junior Contributor

Re: Which is faster to remove leading spaces and zeros: Cast or Trim?

None of them :-)

You can use

LTRIM(STORE_ID, '0 ')

to trim multiple characters.

Enthusiast

Re: Which is faster to remove leading spaces and zeros: Cast or Trim?

Great!  Thanks!  and that will left trim them in any order they appear, for any number of occurences?  In other words it will remove '   0', '0   ', ' 0000', '      00000', etc?

Junior Contributor

Re: Which is faster to remove leading spaces and zeros: Cast or Trim?

Yep, any leading ' ' and '0' in any order will be removed, simply try it.

Re: Which is faster to remove leading spaces and zeros: Cast or Trim?

HI,

 

I have a requirement where a decimal field needs to be implicit decimal point and with leading spaces .

 

Ex : 1234.45 --> '    123445'

Teradata Employee

Re: Which is faster to remove leading spaces and zeros: Cast or Trim?

Hi SankalpChatterj,

 

Here you can see all the steps for the conversion :

with cte_data (num) as (select 1234.45)
select num
     , num * 100
     , cast(num * 100 as integer)
     , cast(cast(num * 100 as integer) as varchar(10))
     , lpad(cast(cast(num * 100 as integer) as varchar(10)), 10, ' ')
  from cte_data;

 

Re: Which is faster to remove leading spaces and zeros: Cast or Trim?

Thanks