RE: SQL insert character field into numeric

Du liebe est ist so toll als Du da bist!

I wasn't expecting '0' concat Digits(MyCol) to fill all the left zeros!










-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Birgitta Hauser
Envoyé : vendredi 3 octobre 2008 12:34
À : 'Midrange Systems Technical Discussion'
Objet : AW: SQL insert character field into numeric

Hi,

You problem is, the numeric values get left adjusted without leading zeros or blanks.
You need the values right adjusted with leading zeros, isn't it?

If the new file is already filled, you might use the following update-statement to correct the column value:
With the following statement the numeric value in MyCol get right adjusted with leading zeros.

Update mytable2
set MyCol = translate(space(length(MyCol)-length(Trim(MyCol))),
'0', ' ') concat MyCol

If you want to change the column in your insert statement, you may use the following conversation:
Insert ....
Select Col1, Col2, ...., '0' concat Digits(MyCol), ....
From ....

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and loosing them? Not training them and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von David FOXWELL
Gesendet: Friday, 03. October 2008 11:46
An: Midrange Systems Technical Discussion
Betreff: SQL insert character field into numeric

Hi!

insert into modifiedFile
select *from originalFile

I just discovered that I can do this, having changed the key in the file from 6,0 to 7character. Only, in the modified file I need to keep my leading zeros. Is there a way of doing this?

Thanks!
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.