How to split a value by separator?

Question

I am running advantage database 10.

One of my field databases Has "customer name" and it is stored as "Lastname, FirstName". I want to be able to extract the first name out of the the field. I figured this would work, but no dice:

SELECT   [Customer Name]
        ,LEFT([Customer Name], CHARINDEX(',', [Customer Name]) - 1) AS [Surname]
        ,REPLACE(SUBSTRING([Customer Name], CHARINDEX(',', [Customer Name]), LEN([FullName])), ',', '') AS [FirstName]
FROM    orders

I received no results. Here is the error message:

poQuery: Error 7200: AQE Error: State = S0000; NativeError = 2158; [iAnywhere Solutions][Advantage SQL Engine]Scalar function not found: charindex -- Location of error in the SQL statement is: 57 (line: 2 column: 32)

Basically I wouldneed | CUSTOMER NAME | Doe, John

to become: |FIRST NAME| LAST NAME | John | Doe

Answer 1

Use the POSITION(',' IN field) syntax.

See the documentation here:

http://devzone.advantagedatabase.com/dz/webhelp/Advantage12/master_string_functions.htm

Your query would look something like this (untested):

SELECT   [Customer Name]
        ,LEFT([Customer Name], POSITION(',' IN [Customer Name]) - 1) AS [Surname]
        ,REPLACE(SUBSTRING([Customer Name], POSITION(',' IN [Customer Name]), LENGTH([FullName])), ',', '') AS [FirstName]
FROM    orders