I need to write an SQL query where I can compare the results with the Excel cube. For the particular table, it was updated for all contract numbers with only 5 digits where they added 0 in front of every five digit number. And they left remaining number i.e 6 digit numbers the same. But, this update is done in ETL package and the added 0 is transparent for me, ie I cannot see it in the table.
For example: for 90034 they added 0 in the beginning of the number i.e 090034 but in the ETL package it appears to be 90034 in the table where 0 in front of 90034 is transparent.
And in Excel cube all the numbers are 6 digit numbers ex., 090034
So, how can I write a SQL query to check if all the 5 digit numbers are been added with the 0 in the beginning and compare it with Excel cube?
Its better for comparison purposes if the data in the database is stored as a varchar or nvarchar since it is clearly NOT a number or it would not have leading zeros.
If however you can't fix that and know the number of total character the string should have, this method would help.
create table #test (myfield int) insert into #test (myfield) values(090034) select * from #test select Right('000000' + cast(myfield as varchar(6)) , 6) from #test