How to compare the first 20 chars onlys

Question

Post reply

How to compare the first 20 chars onlys

mattech06

SSCrazy

Points: 2484
More actions
May 25, 2014 at 7:32 am

#396811

Hi,
I have this code
SELECT
b.TransDate, b.TransType, b.SortCode, b.AccNum, b.TransDesc, b.Debit, b.Credit,
(ISNULL(b.Debit, 0) + ISNULL (b.Credit, 0)) as Amount,
ISNULL(r.OrthoID, 'Need ID') as OrthoID
FROM
EPSBankTransactions b
LEFT JOIN EPSBankRef r on b.TransDesc = r.BankID
but I would only like to try a join on the first 20 characters of b.TransDesc and not the full string. I'm not sure how I would best do that.
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

mattech06 SSCrazy Points: 2484 More actions · Answer 1

sorted it out, didn't think it would work for some reason but

LEFT JOIN EPSBankRef r on SUBSTRING(b.TransDesc, 0, 21) = r.BankID

does the job.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Realize that you lose any benefits of having an index on the TransDesc column. If you can, you may want to consider adding a persisted computed column that stores the first 20 characters of the TransDesc column (LEFT(TransDesc,20)) and build an index on that column.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Lynn Pettis (5/25/2014)
Realize that you lose any benefits of having an index on the TransDesc column. If you can, you may want to consider adding a persisted computed column that stores the first 20 characters of the TransDesc column (LEFT(TransDesc,20)) and build an index on that column.

+1000

But to add to Lynn's good suggestionn, an even better option would be to normalize the data instead of having more than one piece of information in a single column.

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Jeff Moden (5/25/2014)
Lynn Pettis (5/25/2014)
Realize that you lose any benefits of having an index on the TransDesc column. If you can, you may want to consider adding a persisted computed column that stores the first 20 characters of the TransDesc column (LEFT(TransDesc,20)) and build an index on that column.
+1000
But to add to Lynn's good suggestionn, an even better option would be to normalize the data instead of having more than one piece of information in a single column.

another +1000 here

Normalizing the data would be a very good thing.