EXPERT RESPONSE
There are a number of tricks to this type of problem.
First, we need to handle nulls. If you concatenate some character string
values together, and one of them happens to be null, then the entire concatenated
result will be null. So we need COALESCE on each field being concatenated,
to default to an empty string.
Second, we want a method which always works, no matter
whether we are concatenating strings or bits or dates or numeric values
that need to be treated as strings. So we need CAST.
We will use CAST even if the field is already VARCHAR or CHAR, because we want
to constrain the length of the result to a maximum of 3. Here also is where we
need to apply TRIM to extract just the leading non-blank characters
in a CHAR field. Thus in your example, the 2-character value 45 is
left-justified in a 3-character field, and TRIM ensures we will end
up with 045 later.
Finally, after applying COALESCE, CAST, and TRIM, we append the
resulting string to a string of three
zeroes, and then just take the rightmost three characters:
select right('000'
|| coalesce(cast(
trim(field1) as varchar(3))
,'')
, 3 )
|| right('000'
|| coalesce(cast(
trim(field2) as varchar(3))
,'')
, 3 )
|| right('000'
|| coalesce(cast(
trim(field3) as varchar(3))
,'')
, 3 )
from yourtable
If that looks strange to you, here's the Access version:
select right('000'
& iif(isnull(field1), ''
, trim(field1))
, 3 )
& right('000'
& iif(isnull(field2), ''
, trim(field2))
, 3 )
& right('000'
& iif(isnull(field3), ''
, trim(field3))
, 3 )
from yourtable
For More Information
|
