CODEWITHSUNDEEP
sql-server
ajburnett34
ajburnett34

Reputation: 37

Get Substring and Use it In Where Clause

how can I get substring from scanfields (6400,6401,6402) and store it in a variable then use it in a where clause later on? I am trying to get all unique rows that have that substring in the scanfield column.

table 1

scanfields           amount
/411500640036800      50.00
/411500640036800      50.00
/411500640136800      60.00
/411500640236800       70.00

select disintct * from table1 where scanfields = variable goes here

results

scanfields           amount
/411500640036800      50.00
/411500640136800      60.00
/411500640236800       70.00

Upvotes: 0

Views: 52

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415665

select * 
from [Table2]
where scanfields IN (SELECT substring(scanfields, 7,4) from [Table 1])

Note this will tend to be slow. If you have a field like this, where you know portions of the value are distinct elements in advance, you will do much better splitting up the elements as separate fields in the table, so you can index them. Failing that, you can set up a Computed Column, which can also be indexed.

Upvotes: 3

Related Questions