Learn SQL with ChatGPT or when can ChatGPT really help

[frose]

I tried the following in SQL

Code: Select all  Expand view  RUN

SELECT * from vll_term WHERE WORT LIKE '%' + UNHEX('C39F') + '%'

The result is not as expected, maybe there is something wrong with the syntax?

The Google answer is:


ChatGPT:


The correct syntax is therefore:

Code: Select all  Expand view  RUN

SELECT * FROM vll_term WHERE WORT LIKE CONCAT('%', UNHEX('C39F'), '%');

The answer was very helpful for me

[Enrico Maria Giordano]

I can't believe that one would want to use such a totally non-standard SQL statement. What if you want to switch SQL engine in the future (most likely)? I recommend to ever use plain standard SQL.

[frose]

Enrico,
THX for this helpful information, the rest I've done with ChatGPT :


For users who are in an UTF8 environment it's simple:

Code: Select all  Expand view  RUN

SELECT * FROM vll_term WHERE WORT LIKE '%ß%');
 

[Enrico Maria Giordano]

Code: Select all  Expand view  RUN

SELECT * FROM vll_term WHERE WORT LIKE '%ß%'); 

That's it!

[frose]

Enrico,

yes and

Code: Select all  Expand view  RUN

SELECT * FROM vll_term WHERE WORT LIKE CONCAT('%', _utf8'\u00DF', '%');

doesn't work. ChatGPT has obviously made a mistake here.

But when I want to use hex codes for the search string, MariaDB has HEX() and UNHEX().
What is the equivalent in plain SQL?

ChatGPT says:

Certainly, if you're constrained to plain SQL without access to specific MariaDB functions like HEX() and UNHEX(), performing character matching using UTF-8 hexadecimal representations directly within a LIKE statement becomes challenging...

... Unfortunately, in plain SQL without access to database-specific functions, performing character matching using UTF-8 hexadecimal representations directly within LIKE might not be feasible.

[Enrico Maria Giordano]

But when I want to use hex codes for the search string, MariaDB has HEX() and UNHEX().
What is the equivalent in plain SQL?

Why do you want to use hex codes for the search string? This is the first question you have to answer.

[frose]

Enrico,

in German we have a saying: 'you are rowing back'
So we can assume that there ist non corresponding construct in plain SQL!

[Rick Lipkin]

A little AI joke to make you laugh ...

"Open the Pod Bay Doors Hal" .. Sorry Dave, I can't do that"

Gota love it ... Rick

[frose]

ChatGPT cannot (yet) do this, HAL could have done it back in 1968

[frose]

Ok, the movie takes place in 2001.

22 years later than Arthur C. Clarks predicted, it becomes reality for evveryone (HA user):
https://hackaday.com/2022/04/23/2022-sci-fi-contest-your-home-assistant-hal-9000/
https://www.home-assistant.io/integrations/openai_conversation/

The I can say 'Ok Google (HAL), please unlock the front door' and Google (HAL) answers...