[Solved] SQL Query Comparing Strings

[JohnNorris]

Hello,

A novice question on SQL in Base:

I would like to compare two strings in the same row and report the result. I think this is equivalent to "StrComp", but I am unable to get that to work.

For example,

Table
A B C
1 Bob Ivan
2 Jane Bob
3 Jose Jose

I'd like a report that somehow shows A3 has matching data in fields B and C, by flagging the row somehow. Something like?

SELECT A, StrComp("B","C")
FOR Table

I've been trying things and looking up ideas for awhile and finally admit I am stumped. I can make a report of all rows that have the matching data, but want a report with all rows with and without matches.

Thank you for your time,

John

[FJCC]

Will this meet your needs?

Code: Select all

SELECT "A", "B", "C"
FROM "Table_2"
WHERE "B" = "C"

[robleyd]

I think OP wants an extra flag to indicate whether the row has a duplicate or not. My SQL is a bit rusty, but something along the lines of

Code: Select all

SELECT "A", IF ("B"="C",'Duplicate','Unique')  FROM table ....

If Hsql doesn't support IF perhaps CASE might work.

[FJCC]

I think OP wants an extra flag to indicate whether the row has a duplicate or not. My SQL is a bit rusty, but something along the lines of

Code: Select all

SELECT "A", IF ("B"="C",'Duplicate','Unique')  FROM table ....

If Hsql doesn't support IF perhaps CASE might work.

I think that would be

Code: Select all

SELECT "A", CASE WHEN "B" = "C" THEN 'Duplicate' ELSE 'Unique' END AS "Flag"

[JohnNorris]

Will this meet your needs?

Code: Select all

SELECT "A", "B", "C"
FROM "Table_2"
WHERE "B" = "C"

Thank you for the quick reply. That confirmed that I can compare text strings with the equal sign. I wasn't sure about that. I was thinking I would have to use "like" or something. Very helpful.

[JohnNorris]

I think that would be

Code: Select all

SELECT "A", CASE WHEN "B" = "C" THEN 'Duplicate' ELSE 'Unique' END AS "Flag"

[/quote]

Thanks to you both. The above is pretty much what I wanted. I've confirmed that it works with my simple table.

The "CASE" function is not something I've explored before. An If/Then function is very powerful; so many thanks from me.

John

[robleyd]

I assume you are using the embedded hsql V 1.8; if so you might find the hsql documentation for 1.8 a useful resource, particularly Section 9.