Dear All,

I had this records below:

ACode
80
81
817
8171
82

The parameter variable value is '817145779' what is the SQL
statement for getting '8171'

please help

Thanks a lot in advance & Regards
Winanjaya

In article <4434e753.1a82.1681692777 (AT) sybase (DOT) com>, Winanjaya says...
We need a lot more information to help you, especially what is the
criteria which decide that 8171 is the proper result?


--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

'817145779' and '8171' has the most accurate pattern .. as
you see in this record list below:

hopefully you understand what I need ..

Thanks a lot in advance

Regards
Winanjaya

Given what you are asking, the solution would be a sort procedure iterating
through your row values to find the one that is the "most" like the
parameter value you are comparing to.

What are you trying to accomplish?
Usually the functionality goes the other way, for example in a lookup
list box.
--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx...c?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

<winanjaya> wrote

If I understand what you need, take a look at the LIKE function.


In article <4435357c.71ea.1681692777 (AT) sybase (DOT) com>, winanjaya says...
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

I did something kinda similar once - converting from Foxpro to a relational
database and users wanted to list of stuff above and below what the entered.
Not because it was useful, but because that is the way it worked before.....

Anyway, you could do the following in a procedure:

Do a like and append a '%' to the parm.
If you get zero rows, remove one character from the end and try again.
Keep going in a loop until you get at least one row that matches the like.


Without using a procedure, you may also be able to do a like to get the max
record that matches the parm
assuming Acode is your column name this may work:

select max(Acode)
where Acode||'%' like arm (may need to be switched around to arm like
Acode||'%' ).

I haven't tried this specifically so I'm not sure if it works.



<winanjaya> wrote

Hi All,

This is just for a sharing knowledge, I did it by running
below SQL statement:

SELECT *, LOCATE('817145779', ACODE) AS ACODE_PTR FROM ACODE
WHERE ACODE_PTR = 1 ORDER BY ACODE DESC

this will return 8171 .. as I hope

any way .. thanks a lot for any advises.

Regards
Winanjaya