SQL query to add avlue before and after

[wutaguy]

I have numbers where I would like to add a fixed value before the number and a fixed value after the number. The fixed value includes spaces.
e.g. if number is 12456, then I want the output to be H987SSS12456TSSSS, where S is space. Do i need 2 queries?

Thanks for any help.

[weekend00]

if the number is from a field of a table or an expression, just concat the strings before and after the field name/expression:

e.g. select "H987SSS" & myfield &"TSSSS" from ......
(myfield =12456)

[wutaguy]

Sorry, I guess I wasn't clear.

I am importing the numbers from excel file, a total of around 50,000. I would like there to be spaces before and after, not the letter "S".

[weekend00]

you need to do step by step:
1 import
2 update
update tbl set fld="H987SSS" & fld &"TSSSS"

replace S with space.

[wutaguy]

Thanks!!

It works.

[wutaguy]

One more thing.
I need the length for all rows to be 80 characters.

[weekend00]

design the field width to 80,
fill with spaces to 80.

e.g. update table1 set field1=left(field1 & string(80," "),80)