MVSFORUMS.com

NASCAR9

I searched and did not find anything that would help.

I need to update all leading spaces in a varchar column to zeroes.

I tried the following:

set text11 = SUBSTR(text11,01,03) = '000'

sorry I tired more but we lost power and I lost my samples Embarassed

I also tried a LEFT function

Any help will be appreciated.
_________________
Thanks,
NASCAR9

NASCAR9 · Posted: Fri Oct 01, 2010 1:09 pm Post subject:

I got it

set text11 = '000' || (SUBSTR(text11,04,30))
_________________
Thanks,
NASCAR9

papadi · Supermod Joined: 20 Oct 2009 Posts: 594 Topics: 1

What happens if there are other than 3 leading zeros?
_________________
All the best,

di

NASCAR9 · Posted: Fri Oct 01, 2010 1:39 pm Post subject:

papadi,
I need to update a 9 position number. The leading zeroes were not loaded in the table. I will need to update multiply times depending on the number.
This is a one time fix.

'0' || (SUBSTR(text11,02,30))
'00' || (SUBSTR(text11,03,30))
'000'|| (SUBSTR(text11,04,30))
'0000'|| (SUBSTR(text11,05,30))
ect.
_________________
Thanks,
NASCAR9

kolusu · Posted: Fri Oct 01, 2010 2:21 pm Post subject:

Nascar9,

Here is a generic sql (untested) which would give you the desired results. The basic idea is to get the length of the leading spaces length using LTRIM and RTRIM functions and subtracting the lengths.

NASCAR9 · Posted: Fri Oct 01, 2010 2:31 pm Post subject:

Thanks Kolusu,
I will need to test your query. This is what I ran

NASCAR9 · Posted: Tue Oct 05, 2010 6:42 pm Post subject:

I finally got around to testing Kolusu's code.

It works! It is also alot less code then my solution.

Thanks again!