I have tried this:

UPDATE MyFile
SET Tel1 = REPEAT(' ' FOR 10 - LENGTH(TRIM(BOTH ' ' FROM Tel1))) + TRIM(BOTH ' ' FROM Tel1)

But it did not work, because I've noticed REPEAT can't calculate. I've tested with this and it did not work:

SELECT Tel1, REPEAT('-' FOR LENGTH(Tel1))
FROM MyFile

Am I doing something wrong? Is there another way to pad right a phone number, for instance?

Tks,
Marcio

marcioehrlich


The approach is quite simple really - add a lot more more than would be needed and use SUBSTR to just get the bit you want

eg

SELECT Tel1, SUBSTR(Tel1+'---------------------------------------------',1,LENGTH(Tel1))

Roy Lambert [Team Elevate]

Your suggestion pads to the left. Since I needed a right padding, with the Substring approach I had to do this for a 10 char field:

SELECT Tel1, SUBSTRING('----------' + TRIM(BOTH ' ' FROM Tel1) FROM (10 + LENGTH(TRIM(BOTH ' ' FROM Tel1)) - 9) FOR 10)

Anyway, it is odd that SUBSTRING is able to do math opperations but REPEAT is not.

Thanks!,
Marcio

Roy Lambert wrote:

SELECT Tel1, SUBSTR(Tel1+'---------------------------------------------',1,LENGTH(Tel1))

On 12/11/2013 10:06 AM, marcioehrlich wrote:
> But it did not work, because I've noticed REPEAT can't calculate. I've tested with this and it did not work:
>
> SELECT Tel1, REPEAT('-' FOR LENGTH(Tel1))
> FROM MyFile
>
> Am I doing something wrong? Is there another way to pad right a phone number, for instance?

I tired it here and only getting 1 repeat cycle - i'm guessing that's
what you saw.

Looks like a bug to me


Raul

marcioehrlich


>Your suggestion pads to the left. Since I needed a right padding, with the Substring approach I had to do this for a 10 char field:

That's because I never remember which does exactly what

>Anyway, it is odd that SUBSTRING is able to do math opperations but REPEAT is not.

Nah! There's plenty wierder stuff in computing.

Roy Lambert [Team Elevate]

On 12/11/2013 12:16 PM, marcioehrlich wrote:
> Your suggestion pads to the left. Since I needed a right padding, with the Substring approach I had to do this for a 10 char field:

It pad to the right. Your code below pads to the left - - assuming in
both cases the "-" is the padding character.

Left padding generally means putting the pad character(s) in the
beginning of string (------123) and right to the end of string (123-----).


>
> SELECT Tel1, SUBSTRING('----------' + TRIM(BOTH ' ' FROM Tel1) FROM (10 + LENGTH(TRIM(BOTH ' ' FROM Tel1)) - 9) FOR 10)

Raul

Ouch. So I messed up. This is really confusing...
Sorry.
M.

Raul wrote:


It pad to the right. Your code below pads to the left - - assuming in
both cases the "-" is the padding character.

marcioehrlich

>Ouch. So I messed up. This is really confusing...

<giggle>

Roy

Marcio,

<< But it did not work, because I've noticed REPEAT can't calculate. I've
tested with this and it did not work:

SELECT Tel1, REPEAT('-' FOR LENGTH(Tel1))
FROM MyFile >>

You need to give DBISAM a "hint" about how long the REPEAT expression is if
you're using a dynamic expression as the length input:

SELECT Tel1, CAST(REPEAT('-' FOR LENGTH(Tel1)) AS VARCHAR(20))
FROM MyFile

Otherwise, it just uses a length of 1.

Tim Young
Elevate Software
www.elevatesoft.com