April 25, 2008 at 9:26 am
I had one of our programmers come to me this morning and she is trying to update some information in one of our tables. They have the changes in an Excel spreadsheet and have imported it into a work table in SQL. The fields that they are trying to join on are identical. Both are char(14). (I realize that both of these fields should probably be an int but dealing with a legacy system.) However, the following query won't return any results:
SELECT *
FROM dm_prod dm_p JOIN wk_BricsCad w ON dm_p.SsProdid = w.SsProdid
If I open the work table and edit the the ssprodid field and manually delete the extra spaces at the end of the field out and then run the query I get one match.
So, logically, I figured why not run an update to the work table and trim the ssprodid field. That should get the query to match on all 700+ records. Still only one match.
Any ideas as to what is going on here?
April 25, 2008 at 10:28 am
Running:
Update table
Set char_field = Rtrim(char_field)
Does not actually trim as the char data type is fixed width so the data is right padded with spaces. Now changing the data type to varchar(14) and then running the RTRIM will remove spaces on the end.
You could do this in your join:
Select
From
tableA A Join
tableB B On
RTrim(A.char_field) = RTrim(B.char_field)
Of course this is likely to cause a table scan.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 25, 2008 at 10:30 am
The characters are probably some other 'invisible' characters - like tabs or returns.
Use something like this to figure out what the ascii values are...
select distinct ascii(substring(MyColumn, number, 1)) from myTable cross join
(select distinct number from master.dbo.spt_values where number between 0 and 14) a
Use something like this to update them (this could be faster but it will do the job). Alternatively you can hard-code the replaces based on the results of the previous query.
select 0 --to set @@rowcount
while @@rowcount > 0
update myTable set MyColumn = stuff(MyColumn, patindex('%[^0-9]%', MyColumn), 1, '') where MyColumn like '%[^0-9]%'
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 25, 2008 at 10:31 am
Jack Corbett (4/25/2008)
Got me again! 😀
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 25, 2008 at 10:37 am
Have you tried casting the columns as Int in your join?
- 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
April 25, 2008 at 10:41 am
Yes, I have. I get the following when doing that.
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '16954797 ' to data type int.
April 25, 2008 at 11:25 am
Thanks Ryan. I ran what you posted and believe it worked. Thanks for your help.
Is there a way when importing text files to keep those "invisible" characters out to begin with?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply