reverse like?

I am trying to do the equivalent of following pseudo-code:
select id from tablename where %stringcolumn like 'mystring'
so I would have a record returned if the values in stringcolumn were:
ystring
string
tring
ring
etc..
To me this seems like some kind of backwards 'like' but I can't get it
to work.
Any ideas?
ChandyHi Chandy,
I once did that to implement a caller identitfication in our company.
Due to the fact that not every phone extension of the customer was know
by us I cutr the numbers one by one frome the right side on, so it was
something like:
Calling number is: 087776-37 (know Number is 087776-0) the attempts
are:
1: 0877763
2: 087776 (matches 087776 of 087776-0) --Hit
It cut these down one by one in a procedure till I reached a minimun
number which has to be matched. So (my 0.02 $), write a procedure which
does that in a loop.
HTH, Jens Suessmeyer.|||Hi Chandy,
Just trying to get my head round what you want.
At what part do you finish, do you mean also if it contains ing, ng and g?
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<chandy@.totalise.co.uk> wrote in message
news:1137758853.742762.291710@.o13g2000cwo.googlegroups.com...
>I am trying to do the equivalent of following pseudo-code:
> select id from tablename where %stringcolumn like 'mystring'
> so I would have a record returned if the values in stringcolumn were:
> ystring
> string
> tring
> ring
> etc..
> To me this seems like some kind of backwards 'like' but I can't get it
> to work.
> Any ideas?
> Chandy
>|||Hi Tony,
Yes, it would match against ing, ng and g, but not an empty sting or
null.
In reality the data would be domains and sub-domains, so I would be
testing
subdomain.domain.com
and wanting to match a row with
domain.com
but there could be many levels of sub-domain and different levels of
domains in the data so I wouldn't want to keep splitting the string and
re-testing if possible.
Chandy|||Hi Chandy,
This something like what you want....
declare @.seq table (
numb tinyint not null
)
insert @.seq values( 1 )
insert @.seq values( 2 )
insert @.seq values( 3 )
insert @.seq values( 4 )
insert @.seq values( 5 )
insert @.seq values( 6 )
insert @.seq values( 7 )
insert @.seq values( 8 )
insert @.seq values( 9 )
insert @.seq values( 10 )
declare @.source table (
searchtext varchar(500) not null
)
insert @.source values( 'this should tring be shown' )
insert @.source values( 'not this' )
select right( 'mystring', q.numb )
from @.seq q
select *
from @.source s
inner join @.seq q on s.searchtext like '% ' + right( 'mystring',
q.numb ) + ' %'
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<chandy@.totalise.co.uk> wrote in message
news:1137761909.961033.42150@.g43g2000cwa.googlegroups.com...
> Hi Tony,
> Yes, it would match against ing, ng and g, but not an empty sting or
> null.
> In reality the data would be domains and sub-domains, so I would be
> testing
> subdomain.domain.com
> and wanting to match a row with
> domain.com
> but there could be many levels of sub-domain and different levels of
> domains in the data so I wouldn't want to keep splitting the string and
> re-testing if possible.
> Chandy
>|||Chandy, does this work for you?
ALTER TABLE MyTable
ADD ReverseMyColumn AS REVERSE(MyColumn)
CREATE INDEX IX_MyTable_ReverseMyColumn ON MyTable(ReverseMyColumn)
SELECT MyColumn
FROM MyTable
WHERE ReverseMyTable LIKE REVERSE(@.search)+'%'
HTH,
Gert-Jan
chandy@.totalise.co.uk wrote:
> Hi Tony,
> Yes, it would match against ing, ng and g, but not an empty sting or
> null.
> In reality the data would be domains and sub-domains, so I would be
> testing
> subdomain.domain.com
> and wanting to match a row with
> domain.com
> but there could be many levels of sub-domain and different levels of
> domains in the data so I wouldn't want to keep splitting the string and
> re-testing if possible.
> Chandy|||drop table #t
drop table #n
go
create table #t(id int, token varchar(10))
--create table #n(n int)
insert into #t values(1, 'ing')
insert into #t values(2, 'ring')
insert into #t values(3, 'mystring!')
insert into #t values(4, 'mystring')
go
select 1 n
into #n
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11
union all select 12
union all select 13
go
select n,token,substring('mystring', #n.n,100) from #t, #n
where substring('mystring', #n.n,100)=#t.token
n token
-- -- --
6 ing ing
5 ring ring
1 mystring mystring
(3 row(s) affected)|||Thanks to all for the suggestions. The REVERSE column looks the most
interesting, with a trigger to update/populate it I guess, but before I
saw that I simply did a loop over substrings of decreasings length
until I get a result then break the loop. Not as efficient but works
for me just now.
Chandy