SQL simple -> Simple uses of String functions CHR() and ASC()



Here is example that demonstrates simple use of three important of string functions in Jet SQL: CHR(), ASC() and InSTR(). In this example we retrieve customer names that include substring 'ba'. Instead of InSTR(CustomerName, 'ba'), we use here InSTR(CustomerName, CHR(98) & CHR(97)), because every character can be represented by it's ASCII code:

SELECT CustomerName, CHR(98) & CHR(97) AS SubStr,
InSTR(CustomerName, CHR(98) & CHR(97)) AS Position,
ASC('b') AS ASCI_Code_of_b, ASC('a') AS ASCI_Code_of_a
FROM Customers
WHERE InSTR(CustomerName, CHR(98) & CHR(97)) <> 0


The query returns results like these:
CustomerNameSubStrPositionASCI_Code_of_bASCI_Code_of_a
Angelina Albaba129897
Barbara Spearsba19897
Christian Barthba119897
...............


You can find here complete list of ASCII numeric codes: Table of ASCII Characters


sqlexamples.info