RE: SQL Question

Cyndi,


In SQL you need to use the LIKE clause and the % sign eg.

Select * from File1 where FIELD1 like 'Gra%'

The above statement would return all records where FIELD1 contains the
string 'Gra' starting in position 1
eg.
Graham Smith
Gradual Decline
Grateful Dead

but not

The Great Graham is an egotistical SOB
Not So Gradual Decline

To find records where the letter 'Gra' apppears somewhere in the string use

Select * from File1 where FIELD1 like '%Gra%'

In the above example this should return only

Graham Smith
Gradual Decline
Grateful Dead
The Great Graham is an egotistical SOB
Not So Gradual Decline

Can be used in embedded SQL but should be noted that when using a
substitution variable (ie :Var1) that the variable should be padded with %
signs to fill out its length.

Eg if Var1 is 30,A

Select * from File1 where FIELD1 like :Var1

works as in the first example if
Var1 = 'Gra%%%%%%%%%%%%%%%%%%%%%%%%%%%'
but doesn't work if
Var1 = 'Gra%                          '
because then the SQL is looking for strings where the first 3 chars are
'Gra' the 4th char is anything and the last 26 chars are blank.


Regards
Graham
-----Original Message-----
From: Cyndi Bradberry [mailto:CyndiB@IHFA.ORG]
Sent: 02 January 2003 17:08
To: 'MIDRANGE-L@midrange. Com' (E-mail)
Subject: SQL Question


A co-worker has just asked what to use as a variable replacement character
in a select statement.  I can't find anything in any manual. Is there such a
thing ?

For example in dos you could type DEL *.* and it would delete everything.
What do you use in SQL to imitate the * in the previous dos statement ?

Thanks,

Cyndi B.
Boise, ID
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.