Select substring from a column in SQL

Question

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

Is it a datetime column? if yes then use Datepart function to get values w3schools.com/sql/func_datepart.asp — Mahesh Malpani
– Mahesh Malpani, Commented Feb 5, 2015 at 8:00
select count(value) as count from table 1 join table 2 on substring(value,20,10)=substring(code,0,10) where reference='1'. this will work for 2nd query, but it is not way to do it. You are manually giving length and index of each substring. — Ubaid Ashraf
– Ubaid Ashraf, Commented Feb 6, 2015 at 6:46

Ubaid Ashraf · Accepted Answer · 2015-02-06 05:42:15Z

5

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)

edited Feb 6, 2015 at 5:42

answered Feb 5, 2015 at 7:02

Ubaid Ashraf

8855 gold badges16 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

john Over a year ago

thats the same way i have it now,i tried the (*) its still doesnt give a result ,which it should work

Ubaid Ashraf Over a year ago

your are using count(value), just use count() as count or count() as value, it should work

john Over a year ago

i have to specify the column name cause i have multiple columns in that table

Ubaid Ashraf Over a year ago

Count is keyword specifying number of records returned in select query, pleas tell me exactly your requirement

john Over a year ago

if i do the exact same query but have it as substring(value,0,12)=substring(code,0,12) with the respective data to go with it ,it works. but when a value comes in like this 02.02.2011 07:07:00 498-123456-741 and i do this substring(value,20,12)=substring(code,0,12) so that it matches on 498-123456 and give me a count of 1 it doesnt work

|

Abubakr Dar · Accepted Answer · 2015-02-05 07:06:46Z

2

Do this

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

Example

SELECT SUBSTRING(column_name, 4, 3);

answered Feb 5, 2015 at 7:06

Abubakr Dar

4,0764 gold badges24 silver badges28 bronze badges

Collectives™ on Stack Overflow

Select substring from a column in SQL

2 Answers 2

17 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

17 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related