MS SQL 2005, count from substring.

I have 2 tables like this:
table 1:
Number1        Cost
09                   10
08                   20
06                   10
09                    5


and Table2:
Number2
1009
2009
0008
0009

and I want to get some information like this:

Number1        Cost                MathNumber (count the 2 right numbers of Number2 from the Table2)
    09               10                        3
    08               20                        1
    06               10                        0
    09                5                         3

How can I get above infomation with select statement?
JameMeckAsked:
Who is Participating?
 
cyberkiwiCommented:
If you find that either "number" field contains non-numeric values, try this conversion

select t1.Number1, t1.Cost, (
    select count(*) from table2 t2
    where convert(varchar,t1.number1) = right(t2.number2,2))
from table1 t1
0
 
mbprogrammerCommented:
What's the relation between 2 tables?
0
 
JameMeckAuthor Commented:
Just 2 tables, no relation.
Thanks!
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
mbprogrammerCommented:
With same record count?
0
 
Kakhaber SiradzeCommented:
you can create function and use it in select.
CREATE FUNCTION [dbo].[count_number1]
(@number1 varchar(20)
)
RETURNS numeric
AS
BEGIN
	RETURN
     (select count(*) from dbo.table2
     where CHARINDEX(@number1, number2)>0)
END

-------------------------------

select 
  Number1,
  Cost,
  dbo.count_number1(Number1) as MathNumber
from
  dbo.table1

Open in new window

0
 
cyberkiwiCommented:
Hi there,

Looks like you need this

select t1.Number1, t1.Cost, (select count(*) from table2 t2 where t1.number1 = right(t2.number2,2))
from table1 t1
0
 
AmitGKCommented:
Select Number1, Cost,
(Select count(Number2) from Table2 Where SUBSTRING(Number2, 3, 4) = Number1) as 'MathNumber'
From Table1
0
 
JameMeckAuthor Commented:
Thanks!
It is perfect solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.