How to select all data using LIKE clause

forum.td.development (2005-2010)
Zdenko Devcic

How to select all data using LIKE clause

Post by Zdenko Devcic » 16 Nov 2009, 12:54

 Posted by:  Zdenko Devcic 

Hi all,
I have insert data into table like this(SqlBase 7.5):

ID NAME
-----------------
1 LONDON
2 London
3 LoNdON
4 lonDon
.
.
How to select all this data with dfFind with one pattern
Thx

Rainer Ebert

How to select all data using LIKE clause

Post by Rainer Ebert » 16 Nov 2009, 14:05

 Posted by:  Rainer Ebert 

Hi,

select id, name from yourtable where @upper(name) = 'LONDON';

In TD:
'select id, name from yourtable into :nId, :sName where @upper(name) = ' ||
SalStrUpperX( dfName )

Regards,
Rainer

Elio Boezio

How to select all data using LIKE clause

Post by Elio Boezio » 16 Nov 2009, 14:08

 Posted by:  Elio Boezio 

Hi Zdenko

Something like ...

SELECT ... FROM ... WHERE NAME LIKE @UPPER( :sKey )

should work...

HTH

Elio

Elio Boezio

How to select all data using LIKE clause

Post by Elio Boezio » 17 Nov 2009, 07:03

 Posted by:  Elio Boezio 

Hi Zdenko,

From our separate correspondence (and having conducted a small test...) I realise my suggestion was nonsense! I have sent you the suggestion below privately, but I'm posting it here too, partly to redeem myself for my earlier silly suggestion, and partly because this may help someone else ;-)

My test table (called TEST) looks like this:

NAME
============
LoNdOn
LONDON
London
london
Manchester
Londinium
xxxLondon

In SQLTalk, using...

SELECT NAME FROM TEST WHERE @SCAN( NAME, 'London' )= 0 ;

...I get this result:

NAME
============
LoNdOn
LONDON
London
london

... which is what you want, I think...

The @SCAN function performs a CASE-INSENSITIVE match on columns of type CHAR, VARCHAR and LONG VARCHAR, returning a number representing the character position of the first instance of the pattern... hopefully, you're always looking for the pattern at the beginning of the string... because something like 'xxxLondon' would return 3 and therefore be ignored.

HTH

Elio

Rainer Ebert

How to select all data using LIKE clause

Post by Rainer Ebert » 17 Nov 2009, 10:12

 Posted by:  Rainer Ebert 

The best -in my opinion- is: select id, name from test where @upper(name) = 'LONDON';

Elio Boezio

How to select all data using LIKE clause

Post by Elio Boezio » 17 Nov 2009, 10:41

 Posted by:  Elio Boezio 

Rainer, you're absolutely right!

So that's TWO silly suggestions from me... I think I'll go back to just READING the newsgroup entries instead of trying to be clever!

Regards,
Elio

Rainer Ebert

How to select all data using LIKE clause

Post by Rainer Ebert » 24 Nov 2009, 15:08

 Posted by:  Rainer Ebert 

Hi,

please don't write me private emails, please post here.

Regarding your pm: You do not need to set dfFind to uppercase.

You can write 'select id, name from test where @upper(name) =
@upper(:dfFind)' or better 'select id, name from test where @upper(name) =
\'' || SalStrUpperX( dfFind ) || '\''

Regards,
Rainer

Hyacinth Pfeiffer

How to select all data using LIKE clause

Post by Hyacinth Pfeiffer » 15 Jan 2010, 17:47

 Posted by:  Hyacinth Pfeiffer 

Hi,

you should be carefull, if you don´t search for London but for München.
Because SalStrUpper( "München" ) = 'MÜNCHEN' but @UPPER('München') may
be something like 'MüNCHEN' or 'MÜNCHEN' (depends on your country.ini).
Better you build your search code in the following way
.... @UPPER(name) = @UPPER(:dfFind)

regards
Hyacinth

Return to “td.development”

Who is online

Users browsing this forum: [Ccbot] and 0 guests