Joining two numbers

• Stephanus-466433

  SSC-Addicted

  Points: 427

  More actions

  October 9, 2007 at 9:30 am

  #70776

  Hi all,

  Here is my problem. i have to get two numbers out of the database: ownerid and contractnumber.

  then i have to join these two number together and rightjustify and zero fill a 20 character space.

  The owner id is max 5 numbers, but there are owner id's that are only 3 numbers, these have to be zero filled to the max of 5 and the some for contract number with a max of 6 numbers. These have to be transformed before joining them and max filled to 20 char.

  sample output:

  00000000002345001234

  02345 = owner id

  001234 = contract num.

  Thanks for the help

  Stephanus

  Ok, the above sample is just a little part of my query that i use through DTS to output to a text file. i guess what i want to know if its possible to this transformation as part of my query.

• Johan Bijnens

  SSC Guru

  Points: 135369

  More actions

  October 9, 2007 at 10:00 am

  #740429

  I think I got this from SSC, but I cannot find the link:

  -- Written by: Greg Larsen Date: 06/15/2002

  -- All rights reserved Copyright 2002

  set nocount on

  DECLARE @I INT

  DECLARE @C CHAR(8)

  SET @C = ''

  SET @I = 123

  SELECT DATALENGTH(convert(varchar(15),@I))

  select datalength(@c)

  SElect @C= REPLICATE('0', datalength(@C) - datalength(convert(varchar(15),@I))) + CAST(@I AS varchar(15))

  PRINT @C

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• Jez-448386

  SSCarpal Tunnel

  Points: 4594

  More actions

  October 12, 2007 at 9:02 am

  #741612

  Yes you can have this as part of your query:

  declare @owner varchar(5), @contract varchar(6)

  set @owner = '2345'

  set @contract = '123'

  select right (replicate('0',20) + right (replicate('0',5) + @owner,5) + right (replicate('0',6) + @contract,5) ,20)

  Replace the variables with the column names (cast to varchar if necessary).

  Jez

• Stephanus-466433

  SSC-Addicted

  Points: 427

  More actions

  October 12, 2007 at 9:21 am

  #741632

  thanks you so much for the help!!