LTRIM linked field

Question

Post reply

LTRIM linked field

SQL33

SSCrazy

Points: 2044
More actions
January 14, 2008 at 12:32 pm

#73953

I have two tables with customerID number, one table has null padding in front of the number which I can remove using LRTIM, the other does not have the padding What is the correct syntax for linking fields in this case so I can use data from each table in a single query?
Example...
link
table A - customerID ' 1221553'
linked to
table B - customerID '1221553'
Thanks all!

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

select ...

join tableA a join tableB b on ltrim(a.CustomerID) = ltrim(b.CustomerID)

Now, I would advice to cleanup the data instead of following this route

* Noel

SQL33 SSCrazy Points: 2044 More actions · Answer 2

Me too, unfortunately, that's not an available option.

Thanks!

Greg Snidow SSCoach Points: 16108 More actions · Answer 3

Assuming table A is the one with the padding, and that you do not want to see the padding in your view, you could try this:

SELECT

b.ID

FROM tablea a,tableb b

WHERE REPLACE(a.id,' ','') = b.id

Greg

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Actually - if you only have to trim ONE of the tables, then make the non-trimmed on the base table. It will give you a shot at maybe using an index on the non-trimmed table. It may still revert to a table scan, but sometimes it won't.

So - instead, use:

select b.id

from

B INNER JOIN A on b.id=ltrim(a.id)

And i don't think it makes much difference whether you use LTRIM or REPLACE.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Sergiy SSC Guru Points: 110208 More actions · Answer 5

It's acceptable if number of rows never gonna exceed 1000.

Then it's gonna be another topic in "Performance" forum.

If you really cannot trim the ID's on insert (did you think about triggers?) then you better create separate table with 2 columns:

NonTrimmedCustomerID, TrimmedCustomerID

index it and do your joins using this table.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

SQL33 (1/14/2008)
Me too, unfortunately, that's not an available option.
Thanks!

Heh... it's always an option if the nail in the bat is large enough and you can find the designers that put the screws to your data 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Jeff Moden (1/14/2008)
Heh... it's always an option if the nail in the bat is large enough and you can find the designers that put the screws to your data 😉

Jeff - you wouldn't happen to be related to Joe "Bat Man" Clark would ya? :P;):w00t::cool::hehe::blush:

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Heh... "get out there and strike a blow for liberty."

... and we'll leave the lights on for ya!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Greg Snidow SSCoach Points: 16108 More actions · Answer 9

Jeff Moden
Heh... it's always an option if the nail in the bat is large enough and you can find the designers that put the screws to your data 😉

So tell us how you really feel.:P. I particularly liked the one about the pork chop and slingshot.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

Heh... glad someone occasionally gets a laugh out of those... 😛

More on the serious side... the "Bat with a nail in it" is actually a metaphore for what I do...

I'll start by saying that I guess I'm pretty darned lucky. I have folks that actually take the time to understand that when I say something at work, it can pretty well be taken to the bank as being correct. Of course, that reputation doesn't come easily... you always have to be prepared to prove that what you say is true at the drop of a hat... any hat. That's where things like my million row test table (my favorite "bat with a nail in it" 😉 ) came from. I always have the proof at hand even if I don't think they're going to ask for it.

Like Sergiy said to me a long time ago... "A developer must not guess... a developer must KNOW". That's pretty good advice and, used properly, will at least make people stop and think about making a necessary change even when they're opposed to it. Being right (again, you MUST have the proof in the form of code) won't always get the change made, but at least you've shown that you're carry a pretty big bat and you know which end of it to use on who and when... :w00t:

Pick your battles, drive the nail of "proof" into the business end of the bat, and then swing! You don't even need to swing hard! You might miss (less with experience and technique, of course) but people will know that you're actually there to and capable of helping...

... especially when you carry "the bat with the nail in it" or a "slingshot and a bucket of pork chops". :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Key DBA SSCertifiable Points: 6029 More actions · Answer 11

Jeff,

I *have* to know ... Are the pork chops frozen, thawed or cooked? (I am thinking the chop consistency is situational, and if so, please elaborate!)

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

Heh... (laughing out loud, now)... do you think it matters when you get hit in the kisser by a 90 mile an hour porkchop? 😀 😛 😉 :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

sounds like those guys shooting turkeys as plane windshields....:hehe:

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 14

Heh! Similar effect! Gets their attention and it's fun to watch! :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)