July 11, 2011 at 11:33 am
I know for a integer column(say ID) sql server truncates any leading 0's
but i have a particular scenario i need to convert integer column(ID) into varchar and use this column in where clause
Ex:
select cast (c.ID as varchar(7))
from
Masada c with (nolock),
Puma r with (nolock)
where
cast(c.ID as varchar(7)) =r.ID
Let's say i am trying to input a record ID = 0123 in Masada table, ID(which is an int datatype) is being truncated to 123. So when i cast to varchar(7) it will be stored as 123
In Puma table, ID(varchar) is stored as 0123(as the datatype is varchar)....
I am not seeing the expected results because of this truncation issue...
How do i solve my issue
FYI: ID in Masada table is int, ID in Puma table is varchar
Thanks
July 11, 2011 at 11:49 am
Pad it with leading zeroes and use the Right() function.
select Right('00000' + cast(1 as varchar(5)), 5);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 11, 2011 at 12:07 pm
Any other suggestions?? Padding took lot of resources(It took almost 2 sec to execute the query)
July 11, 2011 at 12:11 pm
I wouldn't expect padding to add two seconds unless millions of rows were involved.
Could you possibly publish the actual code you are running, the schema of the tables, and the volumes involved?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 11, 2011 at 12:36 pm
Here is the query
select c.ID
from
Masada c with (nolock),
Puma r with (nolock)
where right('000000' + cast(c.ID as varchar(7)),7) =r.ID
Masada table has around 2 million records
July 11, 2011 at 1:24 pm
sandyinfowave (7/11/2011)
Here is the queryselect c.ID
from
Masada c with (nolock),
Puma r with (nolock)
where right('000000' + cast(c.ID as varchar(7)),7) =r.ID
Masada table has around 2 million records
Try this:
select *
from Masada c
join Puma r on r.ID = c.ID
If you have values in "Puma" which are not integers this will throw an error, but there are ways around it.
If all your Puma IDs will translate to integers, regardless of leading zeroes, there should be no problem.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 11, 2011 at 1:59 pm
Yes, i have values in Puma which are not integers(that's why i am casting my ID in Masada table to varchar)....
but there are ways around it
How do i work around it???
July 11, 2011 at 2:05 pm
Define a CTE for the PUMA table which eliminates rows containing any characters other than 0-9. Then join to that CTE as shown below. In the example, I just used ISNUMERIC(), but that needs to be improved upon. I just wanted to get you the answer quickly so you could see the concept.
declare @Masada table (ID int primary key)
declare @puma table (ID varchar(10) primary key)
insert into @puma
values('0123'),('0155'),('0003'),('0543')
insert into @Masada
select * from @puma
insert into @puma
values('X123')
;with Puma2 as (select * from @puma where isnumeric(ID) = 1) -- this could be enhanced
select c.id as MasadaID,r.ID as PumaID
from @Masada c
join Puma2 r on r.ID = c.ID
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 11, 2011 at 3:00 pm
Yes, i have values in Puma which are not integers(that's why i am casting my ID in Masada table to varchar)....
Although I feel certain this comes too late, I feel compelled to point out that this is poor database design.
You are already encountering problems with it (the one we are dealing with now.)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 11, 2011 at 4:28 pm
Yeah exactly,have to deal with it.....Anyways thanks for u'r help
July 12, 2011 at 8:38 am
Using functions, especially string functions, in the Where clause, will almost always make a query slower. Frequently, much slower. However, when it's the only way you have to get the data you need, slow is better than not at all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply