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.

T-SQL help! Please help!

7 posts in ,  Windows NT General Discussion Last posting was on 2000-07-19 22:49:36.0Z
Donald Cuffee Posted on 2000-07-18 01:42:39.0Z
Message-ID: <3973B60F.12706271@home.com>
Date: Mon, 17 Jul 2000 21:42:39 -0400
From: Donald Cuffee <cuffeed@home.com>
Organization: The Cuffee Family
X-Mailer: Mozilla 4.72 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: T-SQL help! Please help!
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt,sybase.public.sqlserver.general
Lines: 40
NNTP-Posting-Host: cj35360-b.dlcty1.va.home.com 24.18.168.181
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1873 sybase.public.sqlserver.general:3823
Article PK: 1074778

All,
I have this string, 505050500N0904040400S varchar(21), that needs to be
broken up. This string is being pulled from 1 column, coord, in fac
table. How can I break up the string in to 4 separate strings,
505050500 N 0904040400 S?
For example,
505050500N0904040400S represents
505050500 - latitude
N -hemisphere
0904040400 - longitude
S - hemisphere
First I need to check whether the 10th and 21st position is an N or S.
Once I find out what the 10th position is the I can find all the numbers
less than the chosen number.
Let's say that the 10th position is N, then I would search for all
numbers < 505050500. This should bring me back all the "N" latitudes
that are less than 505050500. This information is linked to other
columns therefore bringing information from other columns as well. My
result should resemble the following.

latitude (N) latitude (S) benumber symbol
------------ ------------- ---------- -------
505050500 0904040400 005001 1
494949499 1803232300 050005 5
484848498 0752222500 0900010 2
...... ...... ....... .....

404040400 1802525400 003002 6

I know that if I was given 505050500N0904040400S to place in the
database, I could break it up and put it in separated columns (i.e. Lat,
Long, or Hemisphere column), however, since this string is already
stored in one column, coord, I don't see how you can break it up into 4
different strings. Please help!!!!!!!


Muhammad Rather Posted on 2000-07-18 02:23:41.0Z
Message-ID: <3973BFAD.B31C63F8@home.com>
Date: Mon, 17 Jul 2000 22:23:41 -0400
From: Muhammad Rather <ebs555@home.com>
Organization: @Home Network
X-Mailer: Mozilla 4.61 [en]C-AtHome0407 (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: T-SQL help! Please help!
References: <3973B60F.12706271@home.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt,sybase.public.sqlserver.general
Lines: 50
NNTP-Posting-Host: cc513797-a.plfld1.nj.home.com 24.3.181.168
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1872 sybase.public.sqlserver.general:3822
Article PK: 1074777

Try:

select
substring('505050500N0904040400S',1,9),substring('505050500N0904040400S',10,1),substring('505050500N0904040400S',11,10),substring('505050500N0904040400S',21,1)

Above will be valid if N is always the 10'th char, latitude is always 9
char's long, and S is always the 21'st char and longitude is always 10
chars long.

Donald Cuffee wrote:
>
> All,
> I have this string, 505050500N0904040400S varchar(21), that needs to be
> broken up. This string is being pulled from 1 column, coord, in fac
> table. How can I break up the string in to 4 separate strings,
> 505050500 N 0904040400 S?
> For example,
> 505050500N0904040400S represents
> 505050500 - latitude
> N -hemisphere
> 0904040400 - longitude
> S - hemisphere
> First I need to check whether the 10th and 21st position is an N or S.
> Once I find out what the 10th position is the I can find all the numbers
> less than the chosen number.
> Let's say that the 10th position is N, then I would search for all
> numbers < 505050500. This should bring me back all the "N" latitudes
> that are less than 505050500. This information is linked to other
> columns therefore bringing information from other columns as well. My
> result should resemble the following.
>
> latitude (N) latitude (S) benumber symbol
> ------------ ------------- ---------- -------
> 505050500 0904040400 005001 1
> 494949499 1803232300 050005 5
> 484848498 0752222500 0900010 2
> ...... ...... ....... .....
>
> 404040400 1802525400 003002 6
>
> I know that if I was given 505050500N0904040400S to place in the
> database, I could break it up and put it in separated columns (i.e. Lat,
> Long, or Hemisphere column), however, since this string is already
> stored in one column, coord, I don't see how you can break it up into 4
> different strings. Please help!!!!!!!


Donald Cuffee Posted on 2000-07-19 11:26:27.0Z
Message-ID: <39759062.E0B5CABB@home.com>
Date: Wed, 19 Jul 2000 07:26:27 -0400
From: Donald Cuffee <cuffeed@home.com>
Organization: The Cuffee Family
X-Mailer: Mozilla 4.72 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
To: Muhammad Rather <ebs555@home.com>
Subject: Re: T-SQL help! Please help!
References: <3973B60F.12706271@home.com> <3973BFAD.B31C63F8@home.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt,sybase.public.sqlserver.general
Lines: 69
NNTP-Posting-Host: cj35360-b.dlcty1.va.home.com 24.18.168.181
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1857 sybase.public.sqlserver.general:3820
Article PK: 1074773

Please check out what I just posted in the newsgroup. I am trying to create a view.It will look something like this.
create view coord
as
select fac_sk ------- this is the primary key
substring(coord, 1, 9) as Lat,
substring(coord, 10, 1) as LatDirection,
substring(coord, 11, 10) as Long,
substring(coord, 21, 1) as LongDirection,
from Fac
Go
Hopefully, I will be able to search on Lat, LatDirection,Long, and LongDirection as independent strings.
What do you think?
Thanks,
Donald

Muhammad Rather wrote:

> Try:
>
> select
> substring('505050500N0904040400S',1,9),substring('505050500N0904040400S',10,1),substring('505050500N0904040400S',11,10),substring('505050500N0904040400S',21,1)
>
> Above will be valid if N is always the 10'th char, latitude is always 9
> char's long, and S is always the 21'st char and longitude is always 10
> chars long.
>
> Donald Cuffee wrote:
> >
> > All,
> > I have this string, 505050500N0904040400S varchar(21), that needs to be
> > broken up. This string is being pulled from 1 column, coord, in fac
> > table. How can I break up the string in to 4 separate strings,
> > 505050500 N 0904040400 S?
> > For example,
> > 505050500N0904040400S represents
> > 505050500 - latitude
> > N -hemisphere
> > 0904040400 - longitude
> > S - hemisphere
> > First I need to check whether the 10th and 21st position is an N or S.
> > Once I find out what the 10th position is the I can find all the numbers
> > less than the chosen number.
> > Let's say that the 10th position is N, then I would search for all
> > numbers < 505050500. This should bring me back all the "N" latitudes
> > that are less than 505050500. This information is linked to other
> > columns therefore bringing information from other columns as well. My
> > result should resemble the following.
> >
> > latitude (N) latitude (S) benumber symbol
> > ------------ ------------- ---------- -------
> > 505050500 0904040400 005001 1
> > 494949499 1803232300 050005 5
> > 484848498 0752222500 0900010 2
> > ...... ...... ....... .....
> >
> > 404040400 1802525400 003002 6
> >
> > I know that if I was given 505050500N0904040400S to place in the
> > database, I could break it up and put it in separated columns (i.e. Lat,
> > Long, or Hemisphere column), however, since this string is already
> > stored in one column, coord, I don't see how you can break it up into 4
> > different strings. Please help!!!!!!!

--
=======================================
Donald (Doc) Cuffee
The Cuffee Family
email: cuffeed@home.com
=======================================


Jim Egan Posted on 2000-07-19 22:49:36.0Z
From: Jim Egan <dbaguru@eganomics.com>
Subject: Re: T-SQL help! Please help!
Date: Wed, 19 Jul 2000 17:49:36 -0500
Message-ID: <MPG.13df6a1af0d698a898ab11@199.93.177.77>
References: <3973B60F.12706271@home.com> <3973BFAD.B31C63F8@home.com> <39759062.E0B5CABB@home.com>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.20
Newsgroups: sybase.public.sqlserver.nt,sybase.public.sqlserver.general
Lines: 9
NNTP-Posting-Host: user-33qs04r.dialup.mindspring.com 199.174.0.155
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1847 sybase.public.sqlserver.general:3817
Article PK: 1074772

Yes, you will be able to query against the view and its columns. But, be
aware that you will be using a table scan when querying against those
columns in the view that were constructed using SUBSTRING().
--
Jim Egan [TeamSybase]
Houston, TX
http://www.eganomics.com

Sybase Developers Network
http://sdn.sybase.com/sdn/mec/mec_home.stm


Paul Horan[TeamSybase] Posted on 2000-07-19 14:00:28.0Z
From: "Paul Horan[TeamSybase]" <paulh@twoplus.com>
References: <3973B60F.12706271@home.com> <3973BFAD.B31C63F8@home.com> <39759062.E0B5CABB@home.com>
Subject: Re: T-SQL help! Please help!
Date: Wed, 19 Jul 2000 10:00:28 -0400
Lines: 108
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6700
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700
Message-ID: <v7yrjsY8$GA.260@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt,sybase.public.sqlserver.general
NNTP-Posting-Host: ip154.buffalo4.ny.pub-ip.psi.net 38.26.79.154
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1855 sybase.public.sqlserver.general:3818
Article PK: 1074774

Donald,
If you try and search on a non-leading substring of a column, it's going to
do a sequential scan of the table.

How about defining 4 additional columns on the FAC table, and maintaining
them with Insert/Update triggers? Then you can index those four columns
individually... The index would look something like:

Create trigger whatever
for insert/update ...

Update FAC
set lat = substring( inserted.coord, 1, 9),
latdirection = substring( inserted.coord, 10, 1),
long = substring( inserted.coord, 11, 10 ),
longdirection = substring( inserted.coord, 21, 1)
from inserted
where inserted.fac_sk = FAC.fac_sk

Paul Horan [TeamSybase]
VCI www.twoplus.com
Springfield, MA

"Donald Cuffee" <cuffeed@home.com> wrote in message
news:39759062.E0B5CABB@home.com...
> Please check out what I just posted in the newsgroup. I am trying to
create a view.It will look something like this.
> create view coord
> as
> select fac_sk ------- this is the primary key
> substring(coord, 1, 9) as Lat,
> substring(coord, 10, 1) as LatDirection,
> substring(coord, 11, 10) as Long,
> substring(coord, 21, 1) as LongDirection,
> from Fac
> Go
> Hopefully, I will be able to search on Lat, LatDirection,Long, and
LongDirection as independent strings.
> What do you think?
> Thanks,
> Donald
>
> Muhammad Rather wrote:
>
> > Try:
> >
> > select
> >

substring('505050500N0904040400S',1,9),substring('505050500N0904040400S',10,
1),substring('505050500N0904040400S',11,10),substring('505050500N0904040400S
',21,1)
> >
> > Above will be valid if N is always the 10'th char, latitude is always 9
> > char's long, and S is always the 21'st char and longitude is always 10
> > chars long.
> >
> > Donald Cuffee wrote:
> > >
> > > All,
> > > I have this string, 505050500N0904040400S varchar(21), that needs to
be
> > > broken up. This string is being pulled from 1 column, coord, in fac
> > > table. How can I break up the string in to 4 separate strings,
> > > 505050500 N 0904040400 S?
> > > For example,
> > > 505050500N0904040400S represents
> > > 505050500 - latitude
> > > N -hemisphere
> > > 0904040400 - longitude
> > > S - hemisphere
> > > First I need to check whether the 10th and 21st position is an N or S.
> > > Once I find out what the 10th position is the I can find all the
numbers
> > > less than the chosen number.
> > > Let's say that the 10th position is N, then I would search for all
> > > numbers < 505050500. This should bring me back all the "N" latitudes
> > > that are less than 505050500. This information is linked to other
> > > columns therefore bringing information from other columns as well. My
> > > result should resemble the following.
> > >
> > > latitude (N) latitude (S) benumber symbol
> > > ------------ ------------- ---------- -------
> > > 505050500 0904040400 005001 1
> > > 494949499 1803232300 050005 5
> > > 484848498 0752222500 0900010 2
> > > ...... ...... .......
.....
> > >
> > > 404040400 1802525400 003002 6
> > >
> > > I know that if I was given 505050500N0904040400S to place in the
> > > database, I could break it up and put it in separated columns (i.e.
Lat,
> > > Long, or Hemisphere column), however, since this string is already
> > > stored in one column, coord, I don't see how you can break it up into
4
> > > different strings. Please help!!!!!!!
>
> --
> =======================================
> Donald (Doc) Cuffee
> The Cuffee Family
> email: cuffeed@home.com
> =======================================
>
>


Amol Posted on 2000-07-18 16:36:04.0Z
Message-ID: <39748774.8CE472A0@wilco-int.com>
Date: Tue, 18 Jul 2000 17:36:04 +0100
From: Amol <AKarnik@wilco-int.com>
X-Mailer: Mozilla 4.7 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: T-SQL help! Please help!
References: <3973B60F.12706271@home.com> <3973BFAD.B31C63F8@home.com>
Content-Type: multipart/alternative; boundary="------------E2C94B65C3E556FA87A03359"
Newsgroups: sybase.public.sqlserver.nt,sybase.public.sqlserver.general
Lines: 151
NNTP-Posting-Host: wilcohost-183.wilco-int.com 212.36.174.183
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1870 sybase.public.sqlserver.general:3821
Article PK: 1074776

OR:

Use select charindex ('N', <string>), select charindex('S',<string>) to get the location in the string of characters 'N' and 'S'. You could also use length to find the length of the string if hey are variable length strings. Use these valueswith substring to cut out the 4 strings that you need.

Cheers,
Amol
 

Muhammad Rather wrote:

Try:

select
substring('505050500N0904040400S',1,9),substring('505050500N0904040400S',10,1),substring('505050500N0904040400S',11,10),substring('505050500N0904040400S',21,1)

Above will be valid if N is always the 10'th char, latitude is always 9
char's long, and S is always the 21'st char and longitude is always 10
chars long.

Donald Cuffee wrote:
>
> All,
> I have this string, 505050500N0904040400S varchar(21), that needs to be
> broken up. This string is being pulled from 1 column, coord,  in fac
> table. How can I break up the string in to 4 separate strings,
> 505050500  N  0904040400  S?
> For example,
> 505050500N0904040400S represents
> 505050500 - latitude
> N -hemisphere
> 0904040400 - longitude
> S - hemisphere
> First I need to check whether the 10th and 21st position is an N or S.
> Once I find out what the 10th position is the I can find all the numbers
> less than the chosen number.
> Let's say that the 10th position is N, then I would search for all
> numbers < 505050500. This should bring me back all the "N" latitudes
> that are less than 505050500. This information is linked to other
> columns therefore bringing information from other columns as well. My
> result should resemble the following.
>
> latitude (N)     latitude (S)         benumber    symbol
> ------------    -------------      ----------    -------
> 505050500    0904040400      005001       1
> 494949499    1803232300      050005       5
> 484848498    0752222500      0900010     2
> ......                ......                    .......            .....
>
> 404040400    1802525400       003002      6
>
> I know that if I was given 505050500N0904040400S to place in the
> database, I could break it up and put it in separated columns (i.e. Lat,
> Long, or Hemisphere column), however, since this string is already
> stored in one column, coord, I don't see how you can break it up into 4
> different strings. Please help!!!!!!!


Donald Cuffee Posted on 2000-07-19 11:27:17.0Z
Message-ID: <39759094.AB085C28@home.com>
Date: Wed, 19 Jul 2000 07:27:17 -0400
From: Donald Cuffee <cuffeed@home.com>
Organization: The Cuffee Family
X-Mailer: Mozilla 4.72 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
To: Amol <AKarnik@wilco-int.com>
Subject: Re: T-SQL help! Please help!
References: <3973B60F.12706271@home.com> <3973BFAD.B31C63F8@home.com> <39748774.8CE472A0@wilco-int.com>
Content-Type: multipart/alternative; boundary="------------990F68B1E7F3A674AF92DBC5"
Newsgroups: sybase.public.sqlserver.nt,sybase.public.sqlserver.general
Lines: 190
NNTP-Posting-Host: cj35360-b.dlcty1.va.home.com 24.18.168.181
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1856 sybase.public.sqlserver.general:3819
Article PK: 1074775

Please check out what I just posted in the newsgroup. I am trying to create a view.It will look something like this.
create view coord
as
select fac_sk ------- this is the primary key
substring(coord, 1, 9) as Lat,
substring(coord, 10, 1) as LatDirection,
substring(coord, 11, 10) as Long,
substring(coord, 21, 1) as LongDirection,
from Fac
Go
Hopefully, I will be able to search on Lat, LatDirection,Long, and LongDirection as independent strings.
What do you think?
Thanks,
Donald

Amol wrote:

OR:

Use select charindex ('N', <string>), select charindex('S',<string>) to get the location in the string of characters 'N' and 'S'. You could also use length to find the length of the string if hey are variable length strings. Use these valueswith substring to cut out the 4 strings that you need.

Cheers,
Amol
 

Muhammad Rather wrote:

Try:

select
substring('505050500N0904040400S',1,9),substring('505050500N0904040400S',10,1),substring('505050500N0904040400S',11,10),substring('505050500N0904040400S',21,1)

Above will be valid if N is always the 10'th char, latitude is always 9
char's long, and S is always the 21'st char and longitude is always 10
chars long.

Donald Cuffee wrote:
>
> All,
> I have this string, 505050500N0904040400S varchar(21), that needs to be
> broken up. This string is being pulled from 1 column, coord,  in fac
> table. How can I break up the string in to 4 separate strings,
> 505050500  N  0904040400  S?
> For example,
> 505050500N0904040400S represents
> 505050500 - latitude
> N -hemisphere
> 0904040400 - longitude
> S - hemisphere
> First I need to check whether the 10th and 21st position is an N or S.
> Once I find out what the 10th position is the I can find all the numbers
> less than the chosen number.
> Let's say that the 10th position is N, then I would search for all
> numbers < 505050500. This should bring me back all the "N" latitudes
> that are less than 505050500. This information is linked to other
> columns therefore bringing information from other columns as well. My
> result should resemble the following.
>
> latitude (N)     latitude (S)         benumber    symbol
> ------------    -------------      ----------    -------
> 505050500    0904040400      005001       1
> 494949499    1803232300      050005       5
> 484848498    0752222500      0900010     2
> ......                ......                    .......            .....
>
> 404040400    1802525400       003002      6
>
> I know that if I was given 505050500N0904040400S to place in the
> database, I could break it up and put it in separated columns (i.e. Lat,
> Long, or Hemisphere column), however, since this string is already
> stored in one column, coord, I don't see how you can break it up into 4
> different strings. Please help!!!!!!!

--
=======================================
Donald (Doc) Cuffee
The Cuffee Family
email: cuffeed@home.com
=======================================