SQL Help

• Ram-425798

  Right there with Babe

  Points: 798

  More actions

  February 8, 2011 at 10:02 am

  #99177

  I have the following data to be converted. Can someone help me pl?

  tblA contains

  CustomerKey NVarchar(100)

  CustomerID NVarchar(50)

  CustomerKey CustomerID

  000098878787 284005

  000089898989 284005

  000565656565 084005

  000012323232 004003

  I would like to convert the above to

  CustomerKey Bigint

  CustomerId int

  Any help will be great.

  tx

  ram

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 8, 2011 at 10:14 am

  #1283846

  Ram this seemed a little too straight forward...simple CONVERT commands seem to work fine for me...what am i missing on your question?

  With tblA(CustomerKey,CustomerID)

  As

  (

  SELECT N'000098878787',N'284005' UNION ALL

  SELECT N'000089898989',N'284005' UNION ALL

  SELECT N'000565656565',N'084005' UNION ALL

  SELECT N'000012323232',N'004003'

  )

  SELECT

  CONVERT(BIGINT,CustomerKey) As CustomerKey,

  CONVERT(INT,CustomerID) As CustomerID

  FROM tblA

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Ram-425798

  Right there with Babe

  Points: 798

  More actions

  February 8, 2011 at 12:22 pm

  #1283978

  Hi, thanks for your help.

  I did convert the same but gettign the following error.

  Error Converting data type nvarchar to bigint

• Ram-425798

  Right there with Babe

  Points: 798

  More actions

  February 8, 2011 at 12:34 pm

  #1283990

  Sorry, I didn't read your msg. I did mess the question here.

  as you are aware, the following are the input data.

  tblA contains

  CustomerKey bigint

  CustomerID varchar(25)

  CustomerKey CustomerID

  98878787 284005

  89898989 284005

  56565 084005

  2323232 004003

  I would like to convert the customer key to nvarchar datatype, as follows.

  CustomerKey

  00098878787

  00089898989

  00000056565

  00002323232

  The customer key length may change so I have to precede them with '0's.

  Again, sorry, I didn't read your msg,

  best

  ram

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 8, 2011 at 12:44 pm

  #1283996

  ok, you want the reverse...you have a bigint field, but want preceding zeros.

  the max bigint value is 9,223,372,036,854,775,807

  9223372036854775807

  |______19 digits___|

  123456789012345678

  that's 19 digits.... assuming you want up to 18 preceding zeros, you would do something like this:

  SELECT RIGHT( N'000000000000000000' + CONVERT(Nvarchar(30),CustomerKey),19) From MyTable

  with that example in place, why, oh why do you want preceding zerso...and to display bigint, you need way to many to make it consistent and comfortable/readable.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Ram-425798

  Right there with Babe

  Points: 798

  More actions

  February 8, 2011 at 12:51 pm

  #1284000

  Thanks and you are the Best