string function support

[siddontang]

• ASCII() Return numeric value of left-most character
• BIN() Return a string containing binary representation of a number
• BIT_LENGTH() Return length of argument in bits
• CHAR_LENGTH() Return number of characters in argument
• CHAR() Return the character for each integer passed
• CHARACTER_LENGTH() Synonym for CHAR_LENGTH()
• CONCAT_WS() Return concatenate with separator
• CONCAT() Return concatenated string
• ELT() Return string at index number
• EXPORT_SET() Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get - [ ] an off string
• FIELD() Return the index (position) of the first argument in the subsequent arguments
• FIND_IN_SET() Return the index position of the first argument within the second argument
• FORMAT() Return a number formatted to specified number of decimal places
• HEX() Return a hexadecimal representation of a decimal or string value
• INSERT() Insert a substring at the specified position up to the specified number of characters
• INSTR() Return the index of the first occurrence of substring
• LCASE() Synonym for LOWER()
• LEFT() Return the leftmost number of characters as specified
• LENGTH() Return the length of a string in bytes
• LIKE Simple pattern matching
• LOAD_FILE() Load the named file Add LOAD_FILE() function #8436
• LOCATE() Return the position of the first occurrence of substring
• LOWER() Return the argument in lowercase
• LPAD() Return the string argument, left-padded with the specified string
• LTRIM() Remove leading spaces
• MAKE_SET() Return a set of comma-separated strings that have the corresponding bit in bits set
• MATCH Perform full-text search
• MID() Return a substring starting from the specified position
• NOT LIKE Negation of simple pattern matching
• NOT REGEXP Negation of REGEXP
• OCT() Return a string containing octal representation of a number
• OCTET_LENGTH() Synonym for LENGTH()
• ORD() Return character code for leftmost character of the argument
• POSITION() Synonym for LOCATE()
• QUOTE() Escape the argument for use in an SQL statement
• REGEXP Pattern matching using regular expressions
• REPEAT() Repeat a string the specified number of times
• REPLACE() Replace occurrences of a specified string
• REVERSE() Reverse the characters in a string
• RIGHT() Return the specified rightmost number of characters
• RLIKE Synonym for REGEXP
• RPAD() Append string the specified number of times
• RTRIM() Remove trailing spaces
• SOUNDEX() Return a soundex string Built-in function SOUNDEX not supported #3930
• SOUNDS LIKE Compare sounds Built-in function SOUNDEX not supported #3930
• SPACE() Return a string of the specified number of spaces
• STRCMP() Compare two strings
• SUBSTR() Return the substring as specified
• SUBSTRING_INDEX() Return a substring from a string before the specified number of occurrences of the delimiter
• SUBSTRING() Return the substring as specified
• TRIM() Remove leading and trailing spaces
• UCASE() Synonym for UPPER()
• UNHEX() Return a string containing hex representation of a number
• UPPER() Convert to uppercase

[mrmiywj]

@ngaut I will pick some functions in this issue.

[ngaut]

Cool @mrmiywj
Please feel free to send PR.

[mrmiywj]

@ngaut Actually, the expression folder has been removed. So maybe this issue could be closed.
Any start issue suggested?

[shenli]

@mrmiywj builtin functions are moved here: https://github.com/pingcap/tidb/blob/master/evaluator

[a6802739]

@siddontang , could I have a try for this?

But I want to know how could I run make test or make check for my PR?

I could not see it from CONTRIBUTING.md

[siddontang]

Hi @a6802739

It is very appreciated that you can help us add more functions.

If you add a function, you can first run make test local and then send us the PR after test passes.

[coocood]

@a6802739
Do you have the error like package not found when you run make test on your forked repo?

[a6802739]

@coocood, not found yet. Do you mean I should cross the wall to download some dependency package?

[coocood]

@a6802739
Please describe what you have done and what error you have got, thank you.

[a6802739]

@coocood, after I run make test, this is the error I met.

# github.com/pingcap/tidb/parser
parser/lexer.go:92: undefined: yySymType
parser/misc.go:494: undefined: yySymType
parser/yy_parser.go:60: undefined: yySymType
parser/yy_parser.go:61: undefined: yySymType
parser/yy_parser.go:62: undefined: yySymType
FAIL	github.com/pingcap/tidb [build failed]
# github.com/pingcap/tidb/parser
parser/lexer.go:92: undefined: yySymType
parser/misc.go:494: undefined: yySymType
parser/yy_parser.go:60: undefined: yySymType
parser/yy_parser.go:61: undefined: yySymType
parser/yy_parser.go:62: undefined: yySymType
FAIL	github.com/pingcap/tidb/ast [build failed]
# github.com/pingcap/tidb/parser
/var/folders/72/byjy11cs0dj_z3rjtxnj_nn00000gn/T/go-build175989233/github.com/pingcap/tidb/parser/_test/_obj_test/lexer.go:100: undefined: yySymType
/var/folders/72/byjy11cs0dj_z3rjtxnj_nn00000gn/T/go-build175989233/github.com/pingcap/tidb/parser/_test/_obj_test/misc.go:516: undefined: yySymType
/var/folders/72/byjy11cs0dj_z3rjtxnj_nn00000gn/T/go-build175989233/github.com/pingcap/tidb/parser/_test/_obj_test/yy_parser.go:61: undefined: yySymType
/var/folders/72/byjy11cs0dj_z3rjtxnj_nn00000gn/T/go-build175989233/github.com/pingcap/tidb/parser/_test/_obj_test/yy_parser.go:62: undefined: yySymType
/var/folders/72/byjy11cs0dj_z3rjtxnj_nn00000gn/T/go-build175989233/github.com/pingcap/tidb/parser/_test/_obj_test/yy_parser.go:63: undefined: yySymType
make: *** [gotest] Error 2

[coocood]

@a6802739
Please try make dev

[a6802739]

@coocood , should I cross the wall?

package golang.org/x/tools/go/gcimporter15: unrecognized import path "golang.org/x/tools/go/gcimporter15" (https fetch: Get https://golang.org/x/tools/go/gcimporter15?go-get=1: dial tcp 216.239.37.1:443: i/o timeout

[coocood]

@a6802739
golint imports golang.org/x/tools/go/gcimporter15, make check use golint, so you need to use a proxy to get golint.
But I think you can run make test now.

[a6802739]

@coocood ,Indeed, make test works now.

Thanks a lot.

[coocood]

@a6802739
Feel free to ask anything, looking forward to your PR.

[morgo]

The only two remaining functions are SOUNDEX (plus its alias) and LOAD_FILE. Since both of these are lower priority (and have dedicated sub-issues), I am going to close this issue for now.

Please feel free to re-open if you have any followup questions. Thanks!

[SunRunAway]

Hi, it seems that OCTET_LENGTH is not implemented in TiDB.

I tested on the master branch with commit id 8c20289.

mysql> select OCTET_LENGTH(1);
ERROR 1305 (42000): FUNCTION octet_length does not exist

I've unchecked the OCTET_LENGTH box and created an issue #11195.