Take substrings and put them in IN function

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

SQL Server 2008 Forums

Transact-SQL (2008)

Take substrings and put them in IN function

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-16 : 08:16:39

Hi. I have some data like '411111..........1111' , '412345..........1234' (varchar (20))

What i would like to do is to find the the rows that contain the first portion of the string.
So something like : And Colnumbers in ('411111%','412345%') etc.
This can work with like but i prefer to use in as i would be dealing with a lot of data. Doesn't in except varchars like that? Any help?
Ideally it would be a substring of the 6 first characters and if possible + the last ones (1111,1234,etc).
Thanks.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-16 : 10:57:30

and left(Colnumbers,6) in ('411111','406001') ?
But who i can also check for the right part?
Thanks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-16 : 12:20:44

you can't use wildcards with the IN operator like that. You need:

AND (Colnumbers like '411111%' OR colnumbers like '412345%' ...)

If you have a table of search parameters, you can build q query that joins on that table to get what you want.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-17 : 10:36:36

Hi. Yes the above will work for the first part but how will it work for the last part. I am sorry i don't quite understand your advice.
thanks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-17 : 12:18:46

Given a table containing search parameters, build a SQL statement as an NVARCHAR(4000) that includes one 'Colnumbers like 'nnnnnnn%' for each search parameter, inserting OR between them all.

When you have the SQL statement built, run it with sp_executesql

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-17 : 19:03:41

Still don't get it sorry.
Do you have an example.
For example who would do if for '44441....111' and '44442.....222' ?
Thanks.

ElenaSTL
Starting Member

10 Posts

Posted - 2014-10-20 : 10:55:11

declare @Table1 table (field1 varchar(20))

insert into @Table1
values ('411111oooooo1111'),('412345cccccc1234'),('666666cccccc3313')

select * from @Table1

declare @Table2 table (field2 varchar(30))

insert into @Table2
values ('411111rrrrrrrrrr1111'),('412345sssssssssss1234'),('41bb345sssssssssss12mm34'),('666666sssssssssss3333')

select * from @Table2

declare @FinalTable table (field2 varchar(30),
anotherField varchar(3))

declare @CurrentVar varchar(20)
set @CurrentVar = 'fffffffffffffff'

WHILE (not (@CurrentVar is null))
BEGIN
set @CurrentVar = (select top 1 Field1 from @Table1)

insert into @FinalTable
select field2, 'aaa'
from @Table2
where left(field2,6) = left(@CurrentVar,6)
and right(field2,4) = right(@CurrentVar,4)

delete from @Table1 where Field1 = @CurrentVar
END

select * from @FinalTable

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-21 : 05:49:45

Will have that in mind.
Thanks.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2014-10-21 : 14:19:49

Try something like this:


SELECT *
  FROM (VALUES('411111..........1111'),
              ('412345..........1234')) AS V(s)
       INNER JOIN
       YourTable AS T
       ON T.Colnumbers LIKE REPLACE(V.s, '.', '_'); -- Use '%' instead of '_' if you do not care about exact number of digits to match for '.'

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

Muhammad Al Pasha

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-21 : 16:53:19

Thank you too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources