CODEWITHSUNDEEP
sqlsql-server
john
john

Reputation: 57

Select substring from a column in SQL

How do I match a substring on another table?

Table 1:

Reference | Value
----------+-------
1         | 02.02.2011 07:07:00 498-123456-741
5         | 123-789654-100
5         | 123-789654-100

Table 2:

Reference | Code
----------+-------
5         | 123-789654-700
1         | 498-123456-100

I want to count the value from table one on table 2

select count(value) as count
from table 1 join table 2 on substring(value,0,12)=substring(code,0,12)
where reference='5'

If it the value is present in Table 2 it gives me a count of 2.

select count(value) as count
from table 1 join table 2 on substring(value,20,12)=substring(code,0,12)
where reference='1'

So the first query works fine the second query when a value comes in like 02.02.2011 07:07:00 498-123456-741 it doesn't compare it to table to even though that value is there, in Table 2 it will always be a substring of (0,12).

Upvotes: 1

Views: 33467

Answers (2)

Ubaid Ashraf
Ubaid Ashraf

Reputation: 875

The syntax is like this : SUBSTRING ( expression ,start , length )

For example : 

SELECT x = SUBSTRING('abcdef', 2, 3);

Here is the result set:

x
----------
bcd

You should do like this :

select count(*) as count from 
table 1 join table 2
on substring(value,20,12)=substring(code,0,12)

Upvotes: 5

Abubakr Dar
Abubakr Dar

Reputation: 4078

Do this

SELECT SUBSTRING('w3resource', startingIndex, lengthForRequiredString);

Example

SELECT SUBSTRING(column_name, 4, 3);

Upvotes: 2

Related Questions