Deal with special chars - Trim?

Question

Deal with special chars - Trim?

mattech06

SSCrazy

Points: 2484
More actions
February 12, 2015 at 6:52 am

#294676

Hi I have some sql below..
SELECT
t.Doctor, t.LedgerAmount, t.TransactionDate,
ISNULL(lg.LedgerGrpDesc, 'No Sales Group') AS LedgerGroup
FROM
Transactions t
LEFT OUTER JOIN LedgerGroups lg ON t.LedgerDescription = lg.dbLedgerDesc
WHERE
(lg.LedgerGrpDesc IS NULL OR lg.LedgerGrpDesc <> 'Takings')
AND (t.TransactionDate >= '2014-1-1' OR '2014-1-1' = '')
AND (t.TransactionDate <= '2015-2-12' OR '2015-2-12' = '')
AND t.ClientRef = 'SMI' AND (lg.ClientRef IS NULL OR (lg.ClientRef IS NOT NULL AND lg.ClientRef = 'SMI'))
My problem is that the data in t.LedgerDescription sometimes now has either leading/trailing white space or more likely special chars so the join against lg.dbLedgerDesc doesn't always work.
I can't change the source of the data to strip out special chars/white space so am stuck on how to deal with it.
I tried using LTRIM & RTRIM in the where clause but this doesn't seem to have had any effect...
LEFT OUTER JOIN LedgerGroups lg ON LTRIM(RTRIM(t.LedgerDescription)) = lg.dbLedgerDesc
Any idea's?
thanks,

Viewing 8 posts - 1 through 7 (of 7 total)

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

Stuart Davies SSCoach Points: 19505 More actions · Answer 1

Ideally, you shouldn't be joining on text field - it's not that efficient, especially with functions on it (I know however "needs must" so it's done).

I would look at the following:-

Check both tables for white space / non printed characters on the field you're joining on. then clean up both tables (if possible).

If the data comes from an ETL process check that for how the spurious characters are coming into the database.

As above for any applications that write to the database.

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

mattech06 SSCrazy Points: 2484 More actions · Answer 2

Hi thanks for the reply,

so your suggesting using the trims on both in the where clause/join? do trims work in the where/join?

I'm not able to amend the data that's coming in really I was looking for a solution within my sql above to deal with it at runtime.

I'll try trimming both on the join and see what happens.

thanks,

mattech06 SSCrazy Points: 2484 More actions · Answer 3

trimming on both sides of the join hasn't made any difference

SQLBill SSC Guru Points: 51440 More actions · Answer 4

It is possible the mismatch isn't due to leading/trailing spaces or the special characters. It might be extra spaces inside the text. For example:

These don't have leading/trailing spaces and don't have special characters, but they aren't a match either.

Hello World

If possible, find some rows that are 'supposed' to match and visually compare them. You'll probably find some difference that you weren't aware of.

-SQLBill

ScottPletcher SSC Guru Points: 101120 More actions · Answer 5

Trim functions deal only with spaces. The easiest way to take care of all whitespace is to use REPLACEs, something like this:

LEFT OUTER JOIN LedgerGroups lg ON

REPLACE(REPLACE(REPLACE(LTRIM(t.LedgerDescription),

CHAR(9), ''), --tab

CHAR(10), ''), --lf

CHAR(13), '') --cr

= lg.dbLedgerDesc

Edit: Added LTRIM().

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

mattech06 SSCrazy Points: 2484 More actions · Answer 6

is there a sql command that displays special chars as well, so if a do a select it will display the whole field not just visible text and white spaces - I can try and work it from there?

Stuart Davies SSCoach Points: 19505 More actions · Answer 7

I've not come across one - but that doesn't mean it's not there!

I would use

ASCII(REVERSE(LTRIM(RTRIM(LedgerDescription))))

any numbers below 32 are the ones to watch for, http://www.asciitable.com/ will help with what characters are giving you problems.

It is possible that, once you've identified the offending characters you can remove them with a replace (see further up thread for syntax). You also may have to clean up the data in both tables and any import routines.

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx