| 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. |
|
|
|
|
Take substrings and put them in IN function