Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

SQL Search

9 posts in General Discussion Last posting was on 2008-07-04 07:15:53.0Z
Teddy Posted on 2008-07-01 02:57:13.0Z
Sender: 718c.486998ef.1804289383@sybase.com
From: Teddy
Newsgroups: ianywhere.public.general
Subject: SQL Search
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <48699d09.71e2.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 30 Jun 2008 19:57:13 -0700
X-Trace: forums-1-dub 1214881033 10.22.241.41 (30 Jun 2008 19:57:13 -0700)
X-Original-Trace: 30 Jun 2008 19:57:13 -0700, 10.22.241.41
Lines: 18
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6943
Article PK: 5169

Dear All,

I had with PhoneNo field with record below:

PhoneNo
02211234561
0211234562 -------->
02411234563

what SQL statement should I use if I had variable value
"12345620001" for searching into PhoneNo and I want it
returns "0211234562" ?


please help

TIA
Teddy


Mark Culp Posted on 2008-07-01 16:22:02.0Z
Message-ID: <486A59A6.F1157D14@iAnywhere.com>
From: Mark Culp <reply_to_newsgroups_only_please_nospam_mark.culp@iAnywhere.com>
X-Mailer: Mozilla 4.75 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: SQL Search
References: <48699d09.71e2.1681692777@sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 1 Jul 2008 09:22:02 -0700
X-Trace: forums-1-dub 1214929322 10.22.241.152 (1 Jul 2008 09:22:02 -0700)
X-Original-Trace: 1 Jul 2008 09:22:02 -0700, vip152.sybase.com
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6945
Article PK: 5171

Please explain how your variable is related to the row that
you want to find?
i.e. how is 12345620001 related to 0211234562

Its looks like left(var,7) = right(col,7)
but it could also be left(var,7) = substr(col,4,7)

- Mark

Teddy wrote:
>
> Dear All,
>
> I had with PhoneNo field with record below:
>
> PhoneNo
> 02211234561
> 0211234562 --------?
> 02411234563
>
> what SQL statement should I use if I had variable value
> "12345620001" for searching into PhoneNo and I want it
> returns "0211234562" ?
>
> please help
>
> TIA
> Teddy


Teddy Posted on 2008-07-02 01:03:17.0Z
Sender: 718c.486998ef.1804289383@sybase.com
From: Teddy
Newsgroups: ianywhere.public.general
Subject: Re: SQL Search
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <486ad3d5.1e10.1681692777@sybase.com>
References: <486A59A6.F1157D14@iAnywhere.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 1 Jul 2008 18:03:17 -0700
X-Trace: forums-1-dub 1214960597 10.22.241.41 (1 Jul 2008 18:03:17 -0700)
X-Original-Trace: 1 Jul 2008 18:03:17 -0700, 10.22.241.41
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6946
Article PK: 5172

Hello,
actually that number is a phone dialed number, complete
dialed no format is: 021123456EXTENSION but sometimes users
also able to dial 123456EXTENSION (ie. 12345600001), and we
only stored 021123456 in phonebook table. any idea?

thanks a lot in advance

Rgds

> Please explain how your variable is related to the row
> that you want to find?
> i.e. how is 12345620001 related to 0211234562
>
> Its looks like left(var,7) = right(col,7)
> but it could also be left(var,7) = substr(col,4,7)
>
> - Mark
>
> Teddy wrote:
> >
> > Dear All,
> >
> > I had with PhoneNo field with record below:
> >
> > PhoneNo
> > 02211234561
> > 0211234562 --------?
> > 02411234563
> >
> > what SQL statement should I use if I had variable value
> > "12345620001" for searching into PhoneNo and I want it
> > returns "0211234562" ?
> >
> > please help
> >
> > TIA
> > Teddy


"Frank Ploessel" <fpl... Posted on 2008-07-03 06:54:11.0Z
Subject: Re: SQL Search
From: "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
References: <486A59A6.F1157D14@iAnywhere.com> <486ad3d5.1e10.1681692777@sybase.com>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.udpesk04j0bybf@bonw00164.internal.imsglobal.com>
User-Agent: Opera Mail/9.21 (Win32)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 2 Jul 2008 23:54:11 -0700
X-Trace: forums-1-dub 1215068051 10.22.241.152 (2 Jul 2008 23:54:11 -0700)
X-Original-Trace: 2 Jul 2008 23:54:11 -0700, vip152.sybase.com
Lines: 65
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6951
Article PK: 5176

Teddy,

The first step would be that you try to exactly describe how the phone
number matching should work. This need not be in any specific format or
computer language, it can be in plain English. Then, from there, it should
be straightforward for many of the readers of this forum to suggest the
translation of your description to SQL.

Maybe my assumptions in my parallel post could be a starting point for
this exact description?

Frank

On Wed, 02 Jul 2008 03:03:17 +0200, <Teddy> wrote:

> Hello,
> actually that number is a phone dialed number, complete
> dialed no format is: 021123456EXTENSION but sometimes users
> also able to dial 123456EXTENSION (ie. 12345600001), and we
> only stored 021123456 in phonebook table. any idea?
>
> thanks a lot in advance
>
> Rgds
>
>
>
>
>> Please explain how your variable is related to the row
>> that you want to find?
>> i.e. how is 12345620001 related to 0211234562
>>
>> Its looks like left(var,7) = right(col,7)
>> but it could also be left(var,7) = substr(col,4,7)
>>
>> - Mark
>>
>> Teddy wrote:
>> >
>> > Dear All,
>> >
>> > I had with PhoneNo field with record below:
>> >
>> > PhoneNo
>> > 02211234561
>> > 0211234562 --------?
>> > 02411234563
>> >
>> > what SQL statement should I use if I had variable value
>> > "12345620001" for searching into PhoneNo and I want it
>> > returns "0211234562" ?
>> >
>> > please help
>> >
>> > TIA
>> > Teddy


Teddy Posted on 2008-07-03 14:37:09.0Z
Sender: 61d8.486ce0b0.1804289383@sybase.com
From: Teddy
Newsgroups: ianywhere.public.general
Subject: Re: SQL Search
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <486ce415.62ad.1681692777@sybase.com>
References: <op.udpesk04j0bybf@bonw00164.internal.imsglobal.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 3 Jul 2008 07:37:09 -0700
X-Trace: forums-1-dub 1215095829 10.22.241.41 (3 Jul 2008 07:37:09 -0700)
X-Original-Trace: 3 Jul 2008 07:37:09 -0700, 10.22.241.41
Lines: 88
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6952
Article PK: 5177

Thanks for your advise,
I have these phoneno records below:

PhoneNo.
02223436
02112345 --- this row!
03134567

users are able to dial with the following formats:

format 1:
02112345[extension no]

for example: dial to 02112345 and connect to ext 101
ie. 02112345101 [101 is extension no]
ie. 0211234510225 [10225 is extension no]

format 2:
12345[extension no]

for example: dial to 12345 and connect to ext 101
ie. 12345101 [101 is extension no]
ie. 123452021 [2021 is extension no]

what SQL statement should I use, if I want to find the
phoneno with that conditions?

TIA & Regards

> Teddy,
>
> The first step would be that you try to exactly describe
> how the phone number matching should work. This need not
> be in any specific format or computer language, it can
> be in plain English. Then, from there, it should be
> straightforward for many of the readers of this forum to
> suggest the translation of your description to SQL.
>
> Maybe my assumptions in my parallel post could be a
> starting point for this exact description?
>
> Frank
>
> On Wed, 02 Jul 2008 03:03:17 +0200, <Teddy> wrote:
>
> > Hello,
> > actually that number is a phone dialed number, complete
> > dialed no format is: 021123456EXTENSION but sometimes
> > users also able to dial 123456EXTENSION (ie.
> > 12345600001), and we only stored 021123456 in phonebook
> table. any idea? >
> > thanks a lot in advance
> >
> > Rgds
> >
> >
> >
> >
> >> Please explain how your variable is related to the row
> >> that you want to find?
> >> i.e. how is 12345620001 related to 0211234562
> >>
> >> Its looks like left(var,7) = right(col,7)
> >> but it could also be left(var,7) = substr(col,4,7)
> >>
> >> - Mark
> >>
> >> Teddy wrote:
> >> >
> >> > Dear All,
> >> >
> >> > I had with PhoneNo field with record below:
> >> >
> >> > PhoneNo
> >> > 02211234561
> >> > 0211234562 --------?
> >> > 02411234563
> >> >
> >> > what SQL statement should I use if I had variable
> value >> > "12345620001" for searching into PhoneNo and I
> want it >> > returns "0211234562" ?
> >> >
> >> > please help
> >> >
> >> > TIA
> >> > Teddy
>


"Frank Ploessel" <fpl... Posted on 2008-07-03 16:37:32.0Z
Subject: Re: SQL Search
From: "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
References: <op.udpesk04j0bybf@bonw00164.internal.imsglobal.com> <486ce415.62ad.1681692777@sybase.com>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.udp5srjsj0bybf@bonw00164.internal.imsglobal.com>
User-Agent: Opera Mail/9.21 (Win32)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 3 Jul 2008 09:37:32 -0700
X-Trace: forums-1-dub 1215103052 10.22.241.152 (3 Jul 2008 09:37:32 -0700)
X-Original-Trace: 3 Jul 2008 09:37:32 -0700, vip152.sybase.com
Lines: 132
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6954
Article PK: 5179

Teddy,

I am still not sure this is exactly stating what you want in all details,
just let me put together some assumptions so that you can confirm or
reject them:
* The phoneno column contains phone numbers including area
codes but without extensions.
* The number of digits of the area code is not known exactly.
* The number of digits of the extension is not known exactly.
* Hence, what you are searching is:
- either (your format 1):
the number(s) from the table that match the value of
the variable from the begin of both up to the end of
the column entry, ignoring potentially existing digits
at the end of the variable,
- or (your format 2):
the digits from the begin of the variable to any
arbitrary position matches a substring in the table
from an arbitrary position up to the end.
If it is like this, then the variable value 56789 would match both the
second and third entry for the table, assuming an extension of 6789, or
89. How would you decide in this case?

My impression is that this whole thing would be much more easy if you
would have the area code and the phone number separate. Either in two
separate columns, or at least separated by any non-digit character like /
or - or something similar.
Are you able to achieve this (maybe using a list of known area codes)?

Frank

On Thu, 03 Jul 2008 16:37:09 +0200, <Teddy> wrote:

> Thanks for your advise,
> I have these phoneno records below:
>
> PhoneNo.
> 02223436
> 02112345 --- this row!
> 03134567
>
> users are able to dial with the following formats:
>
> format 1:
> 02112345[extension no]
>
> for example: dial to 02112345 and connect to ext 101
> ie. 02112345101 [101 is extension no]
> ie. 0211234510225 [10225 is extension no]
>
> format 2:
> 12345[extension no]
>
> for example: dial to 12345 and connect to ext 101
> ie. 12345101 [101 is extension no]
> ie. 123452021 [2021 is extension no]
>
> what SQL statement should I use, if I want to find the
> phoneno with that conditions?
>
> TIA & Regards
>
>
>> Teddy,
>>
>> The first step would be that you try to exactly describe
>> how the phone number matching should work. This need not
>> be in any specific format or computer language, it can
>> be in plain English. Then, from there, it should be
>> straightforward for many of the readers of this forum to
>> suggest the translation of your description to SQL.
>>
>> Maybe my assumptions in my parallel post could be a
>> starting point for this exact description?
>>
>> Frank
>>
>> On Wed, 02 Jul 2008 03:03:17 +0200, <Teddy> wrote:
>>
>> > Hello,
>> > actually that number is a phone dialed number, complete
>> > dialed no format is: 021123456EXTENSION but sometimes
>> > users also able to dial 123456EXTENSION (ie.
>> > 12345600001), and we only stored 021123456 in phonebook
>> table. any idea? >
>> > thanks a lot in advance
>> >
>> > Rgds
>> >
>> >
>> >
>> >
>> >> Please explain how your variable is related to the row
>> >> that you want to find?
>> >> i.e. how is 12345620001 related to 0211234562
>> >>
>> >> Its looks like left(var,7) = right(col,7)
>> >> but it could also be left(var,7) = substr(col,4,7)
>> >>
>> >> - Mark
>> >>
>> >> Teddy wrote:
>> >> >
>> >> > Dear All,
>> >> >
>> >> > I had with PhoneNo field with record below:
>> >> >
>> >> > PhoneNo
>> >> > 02211234561
>> >> > 0211234562 --------?
>> >> > 02411234563
>> >> >
>> >> > what SQL statement should I use if I had variable
>> value >> > "12345620001" for searching into PhoneNo and I
>> want it >> > returns "0211234562" ?
>> >> >
>> >> > please help
>> >> >
>> >> > TIA
>> >> > Teddy
>>


Teddy Posted on 2008-07-04 02:54:32.0Z
Sender: 7d35.486d8c28.1804289383@sybase.com
From: Teddy
Newsgroups: ianywhere.public.general
Subject: Re: SQL Search
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <486d90e8.7db8.1681692777@sybase.com>
References: <op.udp5srjsj0bybf@bonw00164.internal.imsglobal.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 3 Jul 2008 19:54:32 -0700
X-Trace: forums-1-dub 1215140072 10.22.241.41 (3 Jul 2008 19:54:32 -0700)
X-Original-Trace: 3 Jul 2008 19:54:32 -0700, 10.22.241.41
Lines: 132
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6955
Article PK: 5180

Thanks a lot Frank,
You're right! .. I have to add 1 field for areacode

Regards
Teddy

> Teddy,
>
> I am still not sure this is exactly stating what you want
> in all details, just let me put together some
> assumptions so that you can confirm or reject them:
> * The phoneno column contains phone numbers including area
> codes but without extensions.
> * The number of digits of the area code is not known
> exactly. * The number of digits of the extension is not
> known exactly. * Hence, what you are searching is:
> - either (your format 1):
> the number(s) from the table that match the value of
> the variable from the begin of both up to the end of
> the column entry, ignoring potentially existing
> digits
> at the end of the variable,
> - or (your format 2):
> the digits from the begin of the variable to any
> arbitrary position matches a substring in the table
> from an arbitrary position up to the end.
> If it is like this, then the variable value 56789 would
> match both the second and third entry for the table,
> assuming an extension of 6789, or 89. How would you
> decide in this case?
>
> My impression is that this whole thing would be much more
> easy if you would have the area code and the phone
> number separate. Either in two separate columns, or at
> least separated by any non-digit character like / or -
> or something similar. Are you able to achieve this (maybe
> using a list of known area codes)?
>
> Frank
>
> On Thu, 03 Jul 2008 16:37:09 +0200, <Teddy> wrote:
>
> > Thanks for your advise,
> > I have these phoneno records below:
> >
> > PhoneNo.
> > 02223436
> > 02112345 --- this row!
> > 03134567
> >
> > users are able to dial with the following formats:
> >
> > format 1:
> > 02112345[extension no]
> >
> > for example: dial to 02112345 and connect to ext 101
> > ie. 02112345101 [101 is extension no]
> > ie. 0211234510225 [10225 is extension no]
> >
> > format 2:
> > 12345[extension no]
> >
> > for example: dial to 12345 and connect to ext 101
> > ie. 12345101 [101 is extension no]
> > ie. 123452021 [2021 is extension no]
> >
> > what SQL statement should I use, if I want to find the
> > phoneno with that conditions?
> >
> > TIA & Regards
> >
> >
> >> Teddy,
> >>
> >> The first step would be that you try to exactly
> describe >> how the phone number matching should work.
> This need not >> be in any specific format or computer
> language, it can >> be in plain English. Then, from there,
> it should be >> straightforward for many of the readers
> of this forum to >> suggest the translation of your
> description to SQL. >>
> >> Maybe my assumptions in my parallel post could be a
> >> starting point for this exact description?
> >>
> >> Frank
> >>
> >> On Wed, 02 Jul 2008 03:03:17 +0200, <Teddy> wrote:
> >>
> >> > Hello,
> >> > actually that number is a phone dialed number,
> complete >> > dialed no format is: 021123456EXTENSION but
> sometimes >> > users also able to dial 123456EXTENSION
> (ie. >> > 12345600001), and we only stored 021123456 in
> phonebook >> table. any idea? >
> >> > thanks a lot in advance
> >> >
> >> > Rgds
> >> >
> >> >
> >> >
> >> >
> >> >> Please explain how your variable is related to the
> row >> >> that you want to find?
> >> >> i.e. how is 12345620001 related to 0211234562
> >> >>
> >> >> Its looks like left(var,7) = right(col,7)
> >> >> but it could also be left(var,7) = substr(col,4,7)
> >> >>
> >> >> - Mark
> >> >>
> >> >> Teddy wrote:
> >> >> >
> >> >> > Dear All,
> >> >> >
> >> >> > I had with PhoneNo field with record below:
> >> >> >
> >> >> > PhoneNo
> >> >> > 02211234561
> >> >> > 0211234562 --------?
> >> >> > 02411234563
> >> >> >
> >> >> > what SQL statement should I use if I had variable
> >> value >> > "12345620001" for searching into PhoneNo and
> I >> want it >> > returns "0211234562" ?
> >> >> >
> >> >> > please help
> >> >> >
> >> >> > TIA
> >> >> > Teddy
> >>
>


"Frank Ploessel" <fpl... Posted on 2008-07-04 07:15:53.0Z
Subject: Re: SQL Search
From: "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
References: <op.udp5srjsj0bybf@bonw00164.internal.imsglobal.com> <486d90e8.7db8.1681692777@sybase.com>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.udragof5j0bybf@bonw00164.internal.imsglobal.com>
User-Agent: Opera Mail/9.21 (Win32)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 4 Jul 2008 00:15:53 -0700
X-Trace: forums-1-dub 1215155753 10.22.241.152 (4 Jul 2008 00:15:53 -0700)
X-Original-Trace: 4 Jul 2008 00:15:53 -0700, vip152.sybase.com
Lines: 164
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6956
Article PK: 5181

Teddy,

If you have the area code in a separate column, then the condition to find
the correct entry would be something like

WHERE Left(:var, Length(phoneno)) = phoneno
OR ( Left(:var, Length(areacode)) = areacode
AND Substr(:var, Length(areacode) + 1, Length(phoneno)) = phoneno)

or

WHERE :var like phoneno || '%' OR :var like areacode || phoneno || '%'

With regards to efficiency, I would not be sure if any of the two versions
above would use an index on phoneno and/or areacode. This would be
relevant only for a large table, and in case this is performance critical,
there might be faster ways to state the search condition.

Frank

On Fri, 04 Jul 2008 04:54:32 +0200, <Teddy> wrote:

> Thanks a lot Frank,
> You're right! .. I have to add 1 field for areacode
>
> Regards
> Teddy
>
>
>> Teddy,
>>
>> I am still not sure this is exactly stating what you want
>> in all details, just let me put together some
>> assumptions so that you can confirm or reject them:
>> * The phoneno column contains phone numbers including area
>> codes but without extensions.
>> * The number of digits of the area code is not known
>> exactly. * The number of digits of the extension is not
>> known exactly. * Hence, what you are searching is:
>> - either (your format 1):
>> the number(s) from the table that match the value of
>> the variable from the begin of both up to the end of
>> the column entry, ignoring potentially existing
>> digits
>> at the end of the variable,
>> - or (your format 2):
>> the digits from the begin of the variable to any
>> arbitrary position matches a substring in the table
>> from an arbitrary position up to the end.
>> If it is like this, then the variable value 56789 would
>> match both the second and third entry for the table,
>> assuming an extension of 6789, or 89. How would you
>> decide in this case?
>>
>> My impression is that this whole thing would be much more
>> easy if you would have the area code and the phone
>> number separate. Either in two separate columns, or at
>> least separated by any non-digit character like / or -
>> or something similar. Are you able to achieve this (maybe
>> using a list of known area codes)?
>>
>> Frank
>>
>> On Thu, 03 Jul 2008 16:37:09 +0200, <Teddy> wrote:
>>
>> > Thanks for your advise,
>> > I have these phoneno records below:
>> >
>> > PhoneNo.
>> > 02223436
>> > 02112345 --- this row!
>> > 03134567
>> >
>> > users are able to dial with the following formats:
>> >
>> > format 1:
>> > 02112345[extension no]
>> >
>> > for example: dial to 02112345 and connect to ext 101
>> > ie. 02112345101 [101 is extension no]
>> > ie. 0211234510225 [10225 is extension no]
>> >
>> > format 2:
>> > 12345[extension no]
>> >
>> > for example: dial to 12345 and connect to ext 101
>> > ie. 12345101 [101 is extension no]
>> > ie. 123452021 [2021 is extension no]
>> >
>> > what SQL statement should I use, if I want to find the
>> > phoneno with that conditions?
>> >
>> > TIA & Regards
>> >
>> >
>> >> Teddy,
>> >>
>> >> The first step would be that you try to exactly
>> describe >> how the phone number matching should work.
>> This need not >> be in any specific format or computer
>> language, it can >> be in plain English. Then, from there,
>> it should be >> straightforward for many of the readers
>> of this forum to >> suggest the translation of your
>> description to SQL. >>
>> >> Maybe my assumptions in my parallel post could be a
>> >> starting point for this exact description?
>> >>
>> >> Frank
>> >>
>> >> On Wed, 02 Jul 2008 03:03:17 +0200, <Teddy> wrote:
>> >>
>> >> > Hello,
>> >> > actually that number is a phone dialed number,
>> complete >> > dialed no format is: 021123456EXTENSION but
>> sometimes >> > users also able to dial 123456EXTENSION
>> (ie. >> > 12345600001), and we only stored 021123456 in
>> phonebook >> table. any idea? >
>> >> > thanks a lot in advance
>> >> >
>> >> > Rgds
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >> Please explain how your variable is related to the
>> row >> >> that you want to find?
>> >> >> i.e. how is 12345620001 related to 0211234562
>> >> >>
>> >> >> Its looks like left(var,7) = right(col,7)
>> >> >> but it could also be left(var,7) = substr(col,4,7)
>> >> >>
>> >> >> - Mark
>> >> >>
>> >> >> Teddy wrote:
>> >> >> >
>> >> >> > Dear All,
>> >> >> >
>> >> >> > I had with PhoneNo field with record below:
>> >> >> >
>> >> >> > PhoneNo
>> >> >> > 02211234561
>> >> >> > 0211234562 --------?
>> >> >> > 02411234563
>> >> >> >
>> >> >> > what SQL statement should I use if I had variable
>> >> value >> > "12345620001" for searching into PhoneNo and
>> I >> want it >> > returns "0211234562" ?
>> >> >> >
>> >> >> > please help
>> >> >> >
>> >> >> > TIA
>> >> >> > Teddy
>> >>
>>


"Frank Ploessel" <fpl... Posted on 2008-07-02 07:25:44.0Z
Subject: Re: SQL Search
From: "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
References: <48699d09.71e2.1681692777@sybase.com>
Content-Transfer-Encoding: 7bit
Message-ID: <op.udnlk5q4j0bybf@bonw00164.internal.imsglobal.com>
User-Agent: Opera Mail/9.21 (Win32)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 2 Jul 2008 00:25:44 -0700
X-Trace: forums-1-dub 1214983544 10.22.241.152 (2 Jul 2008 00:25:44 -0700)
X-Original-Trace: 2 Jul 2008 00:25:44 -0700, vip152.sybase.com
Lines: 37
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6948
Article PK: 5173

Teddy,

From your example, I do not understand how exactly you want to match
between the variable and the phone number.
Just a wild guess is you want to search the phone numbers that end in the
value of the variable with the last four digits removed. In this case, you
would use a condition like

WHERE PhoneNo like '%' || Left(:variable, Length(:variable) - 4)

This assumes you are using embedded SQL or ODBC. If you are using a
different conection method, you may have to adapt this appropriately.

Frank

On Tue, 01 Jul 2008 04:57:13 +0200, <Teddy> wrote:

> Dear All,
>
> I had with PhoneNo field with record below:
>
> PhoneNo
> 02211234561
> 0211234562 -------->
> 02411234563
>
> what SQL statement should I use if I had variable value
> "12345620001" for searching into PhoneNo and I want it
> returns "0211234562" ?
>
>
> please help
>
> TIA
> Teddy