How to remove leading spaces?

[danield]

Hello All,

I have a query that I use to create another table:

SELECT    column1 & " " & column2 INTO table2
FROM        table1
 

The problem is that sometimes there is no value in column1 and then the new entry in table2 is 'space' and then column2. For example if first column has '1' and second column has '2', the new record in the table2 would be '1 2' but if another row has nothing in the column 1 and then '3' in the column2, resulting row in table to would be ' 3'. How to remove the leading space? Or how to prevent the empty space from being created?

Thank you for your time.

danield

[DonRayner]

Use the Ltrim function on your select statement IE

LTrim(column1 & " " & column2)

[danield]

Use the Ltrim function on your select statement IE

LTrim(column1 & " " & column2)

Hello DonRayner,

Thank you very much. Ltrim does exactly what I need :)

danield

[NeoPa]

You can use the Trim() function, but if you are wanting to reflect the actual values where one column is mandatory but the other isn't (EG Name fields where surname is mandatory but the GivenName is optional) then you can make use of the the difference between & and + as string concatenators.

A & B = AB but if B is Null then = A
A + B = AB but if either is Null then = Null

So [GivenName] + ' ' & [Surname] gives the correct result whichever. If [GivenName] is null then the ' ' is reduced to null too by the +.

[danield]

Hello NeoPa,

Thank you very much for this information. I will try it out.

danield

[NeoPa]

No worries :) We're here to help.

Welcome to Bytes!