questions about string value

Question

questions about string value

Curly Bear

SSC Enthusiast

Points: 182
More actions
April 27, 2004 at 10:29 pm

#107645

Hi, All:
   I am having a problem with string value. Here is the case. I have two tables. Their primary keys are a little different.
For example:
   table1: LALA LULU [33q][v0.1]
   table2: LALA LULU [33q] [v0.1]
There is a space in front of the version value which is [v0.1] in table2. Thus, when I join the two tables, they cannot be joined. What I did was using "charindex" function to find the position of "[". However, it gets the position of the first [ appeared. How can I get the second [ in this case? Or do you have any solution to make the string values become the same?? Please help~~~ Thank you!

Viewing 11 posts - 1 through 10 (of 10 total)

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

TriggerMe SSChampion Points: 11752 More actions · Answer 1

Is there not a Primary Key (Integer) to join the 2 tables together? How could you expect perfomance to be good by joining 2 tables on the above information?

Kindest Regards,

Curly Bear SSC Enthusiast Points: 182 More actions · Answer 2

I was asked to make a report. The data source I got does not have any other IDs in the two tables. The only common thing they have is that column. So,... The performance should be bad, but I have to do it....

ButchH Ten Centuries Points: 1073 More actions · Answer 3

How about:

table1: LALA LULU [33q][v0.1]

table2: LALA LULU [33q] [v0.1]

t1.key = SubString(t2.key,1,15) + SubString(t2.key,17,6)

Not elegant, but should work.

Butch

roberthamilton Ten Centuries Points: 1002 More actions · Answer 4

Use the REPLACE function on table2 to remove the unwanted space eg. :

UPDATE table2

SET str_value = REPLACE(str_value, '] [', ']['

Alternatively, if you want to leave the data unchanged, but still do the join, then :

SELECT *

FROM table1 as a JOIN table2 as b on REPLACE(b.str_value, '] [','][' = a.str_value

Don't expect blinding performance on this one though - it's much better to remove the spaces up-front.

Rob

ButchH Ten Centuries Points: 1073 More actions · Answer 5

I agree with Rob on making the keys compatible, however, there may already be lots of code generated with the existing keys. Changing the keys could cause excessive downtime for his system.

Butch

SQLBill SSC Guru Points: 51440 More actions · Answer 6

Here's another way to get rid of the extra space:

DECLARE @mystr VARCHAR(20)

SET @mystr = 'test [a] '

SELECT SUBSTRING(@mystr,1,(LEN(@mystr)-CHARINDEX(' ', (REVERSE(@mystr))))) + SUBSTRING(@mystr, (LEN(@mystr)-CHARINDEX(' ' , (REVERSE(@mystr))))+2, LEN(@mystr))

-SQLBill

roberthamilton Ten Centuries Points: 1002 More actions · Answer 7

Bill

Not just an ugly piece of code, but more complex than necessary and too dependent on all strings being of identical format.

Not clever

gljjr SSChampion Points: 10080 More actions · Answer 8

I'm with Rob on this one. Use the replace function in the join. BTW: Rob is missing the closing parenthesis.

SELECT *

FROM table1 as a JOIN table2 as b on REPLACE(b.str_value, '] [','][') = REPLACE(a.str_value, '] [','][')

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Curly Bear SSC Enthusiast Points: 182 More actions · Answer 9

Hi, All:

Thanks for sharing your answers~ I also think using the replace function is better and easier. Really appreciate your help

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

Sometimes ya just gotta do with the data ya got... The REPLACE answer is a good one. Thanks.

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