Joining tables varchar - string

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

General SQL Server Forums

New to SQL Server Programming

Joining tables varchar - string

Author

Topic

learzbu
Starting Member

27 Posts

Posted - 2011-11-12 : 08:38:56

I wanted to know if it would be possible to join two tables based on the following:

Table 1 Column:

Order_no
040221

Table 2 Column:

Comment
040221/Customer 1

the column in table 1 is a varchar and table 2 is a string, is it possible to truncate the end of the column in table 2 just leaving the digits which i could then convert in to varchar to join on the first table?

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-11-12 : 09:07:35

It is possible but there is no need if all you want is to join those tables. Just try something like this:


Table1 AS T1
INNER JOIN
Table2 AS T2
ON T2.Comment LIKE T1.Order_no + '/%'

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-12 : 11:52:15

In using it like above it will also use an index if present on Order_no column whereas if you apply functions over field for splitting number part the index will be ignored

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learzbu
Starting Member

27 Posts

Posted - 2011-11-13 : 08:06:30

Thanks Alot!

learzbu
Starting Member

27 Posts

Posted - 2011-11-13 : 11:00:43

select scheme.opheadm.order_no, scheme.porecpm.order_no, delivery_no, invoice_no,
customer, qty_received
from scheme.opheadm join scheme.porecpm on (scheme.porecpm.commnt like (scheme.opheadm.order_no + '/%'))
where
effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'

I tried this but I get blank set of results....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-13 : 11:49:01

quote:
Originally posted by learzbu

select scheme.opheadm.order_no, scheme.porecpm.order_no, delivery_no, invoice_no,
customer, qty_received
from scheme.opheadm join scheme.porecpm on (scheme.porecpm.commnt like (scheme.opheadm.order_no + '/%'))
where
effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'

I tried this but I get blank set of results....

first check if you've any data for given period in the tables


select count(*) from scheme.opheadm where
	effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learzbu
Starting Member

27 Posts

Posted - 2011-11-13 : 12:16:09

Ya there's data for that period, I have 4 rows. But the join won't find them, A left join will give me the 4 rows but null data for the results from scheme.porecpm

learzbu
Starting Member

27 Posts

Posted - 2011-11-13 : 12:19:12

order_no order_no delivery_no invoice_no customer qty_received
004218 NULL NULL OP/I010359 8051 NULL
004213 NULL NULL OP/I010358 8050 NULL
004220 NULL NULL OP/I010361 8024 NULL
004221 NULL NULL OP/I010362 8038 NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-13 : 12:20:17

quote:
Originally posted by learzbu

order_no order_no delivery_no invoice_no customer qty_received
004218 NULL NULL OP/I010359 8051 NULL
004213 NULL NULL OP/I010358 8050 NULL
004220 NULL NULL OP/I010361 8024 NULL
004221 NULL NULL OP/I010362 8038 NULL

and how are corresponding records in other table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learzbu
Starting Member

27 Posts

Posted - 2011-11-13 : 12:26:03

order_no commnt delivery_no qty_received
070218 004218/ACCESS RENEWA 730649 18

This is the corresponding table, it's the comment that i'm trying to match to the first table based on the digits in the comment

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-13 : 12:30:37

is this any better?


select	 scheme.opheadm.order_no, scheme.porecpm.order_no, delivery_no, invoice_no,
customer, qty_received
from scheme.opheadm join scheme.porecpm on (ltrim(rtrim(scheme.porecpm.commnt)) like (ltrim(rtrim(scheme.opheadm.order_no)) + '/%'))
where
effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learzbu
Starting Member

27 Posts

Posted - 2011-11-13 : 12:36:16

Yes!! thanks!!! that's gotten it. Was it reading blank spaces? Was that the issue?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-13 : 12:37:46

yep..it had some hidden space

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources