joining on substring? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

joining on substring?

Author

Topic

memarcie
Yak Posting Veteran

91 Posts

Posted - 2007-12-06 : 10:39:51

Hi, I want to join two tables on a text field that may have extra characters in one of the tables.

Table1
Tag
AMBEEG-F
BPHO-P
SRCH AUR-F

Table2
Tag
AMBEEG
BPHO
SRCH AUR

Is there a way to join these two tables together on the tag field?

Thanks,
Marcie

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-06 : 10:43:37

Its possible but it will kill the performance if your table is huge.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-12-06 : 10:44:22

[code]
table1 t1
join table2 t2
on t1.tag like t2.tag + '%'
[/code]

...but seriously, YUK!

Em

memarcie
Yak Posting Veteran

91 Posts

Posted - 2007-12-06 : 10:49:35

Thanks Em, I agree as well ICK! But it worked.

Thanks again.

memarcie
Yak Posting Veteran

91 Posts

Posted - 2007-12-06 : 10:59:39

I spoke too soon. It didn't join the two tables together.

I tried this also and it didn't work.

table1 t1
join table2 t2
on t1.tag + '%' like t2.tag + '%'

Any other suggestions?

memarcie
Yak Posting Veteran

91 Posts

Posted - 2007-12-06 : 11:11:15

got it
I had to add rtrim

table1 t1
join table2 t2
on rtrim(t1.tag) + '%' like t2.tag + '%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-07 : 02:12:42

Thats what you need to take care when using char datatype

Madhivanan

Failing to plan is Planning to fail

memarcie
Yak Posting Veteran

91 Posts

Posted - 2007-12-10 : 11:21:22

One more question. How to I pull off the value of Tag in Table on, to the left of the '-'

Table1
Tag
AMBEEG-F
BPHO-P
SRCH AUR-F

I want to return the results
AMBEEG
BPHO,
SRCH AUR

Thanks,
Marcie

memarcie
Yak Posting Veteran

91 Posts

Posted - 2007-12-10 : 12:11:07

Figured it out if anyone is interested.

select LEFT(tag, PATINDEX('%-%', tag)-1) from table1.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources