Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL syntax

SQL syntax

From
Dale Seaburg
Date:
My mind is drawing a blank.  Not sure where to go to find the answer.  
Here is the statement in C#:

sSQL = "SELECT \"Image_Filename\" FROM \"Instruments\" WHERE ";

To finish off the WHERE clause, I need to look at the first 2 letters, 
like "D:".  My question is how do I specify in the WHERE clause, to look 
at the first 2 characters in the Image_Filename column?  What is the 
correct SQL syntax for looking at just a portion of a column?

I feel so dumb...  This can't be that hard, but I am exhausted and 
running out of ideas.  I need someone to give me a boost!  ;-)

Thanks,
Dale.

Re: SQL syntax

From
Adrian Klaver
Date:
On 03/02/2018 04:36 PM, Dale Seaburg wrote:
> My mind is drawing a blank.  Not sure where to go to find the answer. 
> Here is the statement in C#:
> 
> sSQL = "SELECT \"Image_Filename\" FROM \"Instruments\" WHERE ";
> 
> To finish off the WHERE clause, I need to look at the first 2 letters, 
> like "D:".  My question is how do I specify in the WHERE clause, to look 
> at the first 2 characters in the Image_Filename column?  What is the 
> correct SQL syntax for looking at just a portion of a column?

SELECT "Image_Filename" FROM "Instruments" WHERE "ImageFilename" LIKE 'D:%';

If you want case insensitive then ILIKE. For more info:
https://www.postgresql.org/docs/10/static/functions-matching.html#FUNCTIONS-LIKE

> 
> I feel so dumb...  This can't be that hard, but I am exhausted and 
> running out of ideas.  I need someone to give me a boost!  ;-)
> 
> Thanks,
> Dale.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: SQL syntax

From
Ken Tanzer
Date:
On Fri, Mar 2, 2018 at 4:41 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 03/02/2018 04:36 PM, Dale Seaburg wrote:
My mind is drawing a blank.  Not sure where to go to find the answer. Here is the statement in C#:

sSQL = "SELECT \"Image_Filename\" FROM \"Instruments\" WHERE ";

To finish off the WHERE clause, I need to look at the first 2 letters, like "D:".  My question is how do I specify in the WHERE clause, to look at the first 2 characters in the Image_Filename column?  What is the correct SQL syntax for looking at just a portion of a column?

 SELECT "Image_Filename" FROM "Instruments" WHERE "ImageFilename" LIKE 'D:%';

If you want case insensitive then ILIKE. For more info:
https://www.postgresql.org/docs/10/static/functions-matching.html#FUNCTIONS-LIKE


It's not clear what kind of test you're trying to apply to those first two characters.  If you want to pull them out to use in an expression (e.g., if you want to see if they are greater or less than something else), you can use SUBSTRING or LEFT.

SELECT SUBSTRING('abc' FROM 1 FOR 2),LEFT('abc',2),LEFT('abc',2)<'aa' AS is_less,LEFT('abc',2)>'aa' AS is_more;
 substring | left | is_less | is_more 
-----------+------+---------+---------ab        | ab   | f       | t
(1 row)
Cheers,
Ken

 
-- 
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: SQL syntax

From
Tom Lane
Date:
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 03/02/2018 04:36 PM, Dale Seaburg wrote:
>> To finish off the WHERE clause, I need to look at the first 2 letters, 
>> like "D:".  My question is how do I specify in the WHERE clause, to look 
>> at the first 2 characters in the Image_Filename column?  What is the 
>> correct SQL syntax for looking at just a portion of a column?

> SELECT "Image_Filename" FROM "Instruments" WHERE "ImageFilename" LIKE 'D:%';

Another way is to use the substring() function:

SELECT "Image_Filename" FROM "Instruments"
  WHERE substring("ImageFilename", 1, 2) = 'D:';

or if you want to use the SQL committee's COBOLish syntax:

SELECT "Image_Filename" FROM "Instruments"
  WHERE substring("ImageFilename" FROM 1 FOR 2) = 'D:';

Depending on what you're doing, either the pattern-match way or the
substring way might be more convenient.  The performance implications
are different too, though that won't matter to you unless you're dealing
with so much data that you want to create a specialized index to make
queries of this form faster.

            regards, tom lane

Re: SQL syntax

From
Rob Sargent
Date:

> On Mar 2, 2018, at 5:41 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
>> On 03/02/2018 04:36 PM, Dale Seaburg wrote:
>> My mind is drawing a blank.  Not sure where to go to find the answer. Here is the statement in C#:
>> sSQL = "SELECT \"Image_Filename\" FROM \"Instruments\" WHERE ";
>> To finish off the WHERE clause, I need to look at the first 2 letters, like "D:".  My question is how do I specify
inthe WHERE clause, to look at the first 2 characters in the Image_Filename column?  What is the correct SQL syntax for
lookingat just a portion of a column? 
>
> SELECT "Image_Filename" FROM "Instruments" WHERE "ImageFilename" LIKE 'D:%';
>
> If you want case insensitive then ILIKE. For more info:
> https://www.postgresql.org/docs/10/static/functions-matching.html#FUNCTIONS-LIKE
>
>> I feel so dumb...  This can't be that hard, but I am exhausted and running out of ideas.  I need someone to give me
aboost!  ;-) 
>> Thanks,
>> Dale.
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
Use the tilda, Luke.
filename ~ ‘^D:’