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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in February 2004