Trim question? 1

[Palmyra]

I need to join two tables on a common field. However, the data in one table gets modified - '(data)' gets appended.

So, I need my query two join on two fields, but trim one of them up to '('.

My attempt is:


SELECT wa_batchinfo.clstatusdesc, udfvlst.uddesc
FROM udfvlst
INNER JOIN wa_batchinfo
ON udfvlst.uddesc = LEFT(wa_batchinfo.clstatusdesc, CHARINDEX('(', wa_batchinfo.clstatusdesc)-1)

but I get:

Msg 536, Level 16, State 5, Line 2
Invalid length parameter passed to the SUBSTRING function.

any help appreciated.

 

[gmmastros]

The problem you are having is that not ALL of your data has (data) appended to it.

Take a look at this:

Declare @Test VarChar(20)
Set @Test = 'Blah (data)'

Select CHARINDEX('(', @Test)-1

Set @Test = 'Blah'
Select CHARINDEX('(', @Test)-1

Set @Test = NULL
Select CHARINDEX('(', @Test)-1

Notice that the first is fine (it returns 5). The second returns -1 and the 3rd returns NULL. Now, look at how you are using the results of the code above...

Left(data, CharIndexOfData)

When the first part returns -1, you end up with:

Left(Data, -1)

The easiest way to fix this problem is to give the CharIndex function something to find so that it never returns a -1. Like this...

Declare @Test VarChar(20)
Set @Test = 'Blah (data)'

Select CHARINDEX('(', @Test [!]+ '('[/!])-1

Set @Test = 'Blah'
Select CHARINDEX('(', @Test [!]+ '('[/!])-1

Set @Test = NULL
Select CHARINDEX('(', @Test [!]+ '('[/!])-1

Run the code above, and you will see 5, 4, NULL. Passing this in to the left function will return what you want.

SELECT wa_batchinfo.clstatusdesc, udfvlst.uddesc
FROM udfvlst
INNER JOIN wa_batchinfo
    ON udfvlst.uddesc = LEFT(wa_batchinfo.clstatusdesc, CHARINDEX('(', wa_batchinfo.clstatusdesc[!] + '('[/!])-1)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Palmyra]

Thank you!