🚀 go-pugleaf

On Mon, 20 Oct 1997 09:40:47 GMT, "Roger Beardsworth"
<roger.beardsworth@dai.nospam.co.uk> wrote:

>1. You could change your server sort-order to case-insensitive. However,
>this requires rebuilding the database and all the data within it. And you
>can't use normal backup and restore.
>Alternatively, you could convert both field name and constant to uppercase
>- but that probably will negatively affect performance if an index could be
>used.
>2. Not unless you have an IDENTITY column (or similar) in the table.
>Relational database tables are not supposed to be ordered in any way.
>3. I would recommend SQL Server Books Online (comes with the setup CD), as
>it has a decent search engine.
>
>Regards, Roger.
>
>Heinz A. Krebs Dipl.-Ing. <hkrebs@fbch.tuwien.ac.at> wrote in article
><344B1EAD.E25@fbch.tuwien.ac.at>...
>> servas!
>> 
>> i have three small questions about sql-queries:
>> 
>> 1. how can i make a case-insensitive selection? if i make the following:
>>      SELECT titel FROM database WHERE titel LIKE '%apple%' 
>>    i don't get the entries with 'Apple', but i want them all!


Try

SELECT titel FROM database WHERE UPPER(titel) LIKE '%APPLE%'


>> 
>> 2. can i retrieve a record by his recordnumber like
>>      SELECT titel, rezept FROM database WHERE recordnumber=4162
>> 
>> 3. where can i get a good explanation of SQL-statements on the web to
>>    avoid further boring questions by me?  ;-)
>> 
>> 
>> ciao
>>   H.
>> 
>> ***********************************************************************
>>                    KOS - Kochbuch Online System
>> 
>>   45000 Kochrezepte online  siehe unter http://www.styria.co.at/kos/
>> ***********************************************************************
>> Heinz A. Krebs Dipl.Ing.        voice:  43-1-4077234 or 43-1-58801-5011
>> Vienna University               fax:                       43-1-4077235
>>   of Technology                 email:         hkrebs@fbch.tuwien.ac.at
>> Institute of Organic Chemistry  WWW: http://info.tuwien.ac.at/
>>   Department Naturstoffsynthese                      orgchem/krebs/
>> Getreidemarkt 9/9/154           fido:                          2:310/67
>> A - 1060 Wien                   mailbox:                   43-1-4077235
>> ***********************************************************************
>> 

Heinz,

Here are the answers you requested :

1. SELECT titel FROM database WHERE upper(titel) LIKE '%APPLE%' 
2. The way to do this is to select the data into a temp table.  Then append
a column to the table of type identity.  The numbers will be assigned
automaticaly and you can select where the identity value = 4162 - Here is
the code


create table test1 (
test1 char(3)
)
go
insert into test1 values ("X")
insert into test1 values ("XX")
insert into test1 values ("XXX")
go

drop proc TP1 
go
create proc TP1 @tablename varchar(30),@RecordNo int as
declare @RecordNo2 varchar(30)
if exists (select * from tempdb..sysobjects where name = "##test")
drop table ##test
exec ("select * into ##test from "+@tablename)
exec ("alter table ##test add Ident int identity")
select @RecordNo2 = convert(varchar,@RecordNo)
exec ("select * from ##test where identitycol = "+@RecordNo2)

exec TP1 "test1", 3

3.  There are a number of good books and Mags you can look at for details
of SQL on the Web.  It depends on the way you are connecting to the sql
server and what your web server will be.  For IDC try SQL Server unleashed
this is a simple way to get connected and very flexible - I have
implemented an intranet using this method.  For mags look at either
windowsNT Mag at www.winntmag.com or Microsoft Interactive developer at
www.microsoft.com/mind  Lots of great stuff at both sites.

As for more questions - please post as many as possible.  Its a public
forum and I bet If you are having a problem then there are 10 of us out
here having the same one.  If you require code for any of the above please
contact me.


-- 
I hope this was of use to you

Steve Robinson MCP (SQL /NT)

Steve@Samurai1.demon.co.uk
 

This is a multi-part message in MIME format.
--------------D31BAC72435AC748BEF2D0A5
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit



Heinz A. Krebs Dipl.-Ing. wrote:

> servas!
>
> i have three small questions about sql-queries:
>
> 1. how can i make a case-insensitive selection? if i make the following:
>      SELECT titel FROM database WHERE titel LIKE '%apple%'
>    i don't get the entries with 'Apple', but i want them all!
>
> 2. can i retrieve a record by his recordnumber like
>      SELECT titel, rezept FROM database WHERE recordnumber=4162
>

The record number is not support in SQL.But you can do something
similar.When a select return you a set, be sure you always sort the result
set by
the same field and   do that

declare @field varchar(10)   <- the same lenght and datatype of the field
you need
declare @rownumber

select @rownumber = 4162

declare scur scroll cursor for
  select field from table order by field

open scur

fetch first from scur into @field
if @@CURSOR_ROWS >= @rownumber
begin
   fetch absolute @rownumber from scur into @field
end
else
begin
   select @field = null
end

close scur
deallocate scur

if @field is not null
begin
  select * from table where field = @field   <- it's the 4162nd row
end
else
begin
   print 'This row does not exist.'
end

with this example, you use CURSOR statement. It's look like a recno.





> 3. where can i get a good explanation of SQL-statements on the web to
>    avoid further boring questions by me?  ;-)
>

Search for a book of SQL 92    by   C.J. Date (the one who think SQL)


> ciao
>   H.
>
> ***********************************************************************
>                    KOS - Kochbuch Online System
>
>   45000 Kochrezepte online  siehe unter http://www.styria.co.at/kos/
> ***********************************************************************
> Heinz A. Krebs Dipl.Ing.        voice:  43-1-4077234 or 43-1-58801-5011
> Vienna University               fax:                       43-1-4077235
>   of Technology                 email:         hkrebs@fbch.tuwien.ac.at
> Institute of Organic Chemistry  WWW: http://info.tuwien.ac.at/
>   Department Naturstoffsynthese                      orgchem/krebs/
> Getreidemarkt 9/9/154           fido:                          2:310/67
> A - 1060 Wien                   mailbox:                   43-1-4077235
> ***********************************************************************



--
Benoit Chailler
mailto:benoit@ic2c.com
Images Coast 2 Coast
http://www.ic2c.com


--------------D31BAC72435AC748BEF2D0A5
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Benoit Chailler
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             Benoit Chailler
n:              Chailler;Benoit
org:            Images Coast 2 Coast
adr:            1000 St-Antoine #303;;;Montreal;Quebec;H3C 3R7;Canada
email;internet: benoit@ic2c.com
title:          DBA
tel;work:       (514) 868-0874
tel;fax:        (514) 868-0875
tel;home:       (514) 237-3141
x-mozilla-cpt:  ;0
x-mozilla-html: FALSE
version:        2.1
end:            vcard


--------------D31BAC72435AC748BEF2D0A5--