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

Show quoteHide quote

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.

Show quoteHide quote

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


cha***@totalise.co.uk wrote:
Show quoteHide quote

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

Can't we use variables in OPENQUERY, FREETEXT("@searchstring")?
inserting single precision data into sql server float column
SQL dilema
CTE vs. Table Variable (Paging)
can't insert into ...because of nulls problme!