Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "hive string function arrangement". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. String length function: length

Syntax: length (string A)

Return value: int

Description: returns the length of string A

For example:

Hive > select length ('abcedfg') from lxw_dual

seven

two。 String inversion function: reverse

Syntax: reverse (string A)

Return value: string

Description: returns the inversion result of the string A

For example:

Hive > select reverse (abcedfg') from lxw_dual

Gfdecba

3. String concatenation function: concat

Syntax: concat (string A, string B...)

Return value: string

Description: returns the result of the input string connection. Any input string is supported.

For example:

Hive > select concat ('abc','def','gh') from lxw_dual

Abcdefgh

4. Delimited string concatenation function: concat_ws

Syntax: concat_ws (string SEP, string A, string B...)

Return value: string

Description: returns the result after the input string is concatenated. SEP represents the delimiter between each string.

For example:

Hive > select concat_ws (',', 'abc','def','gh') from lxw_dual

Abc,def,gh

5. String interception function: substr,substring

Syntax: substr (string A, int start), substring (string A, int start)

Return value: string

Description: returns the string A from the start position to the end of the string

For example:

Hive > select substr ('abcde',3) from lxw_dual

Cde

Hive > select substring ('abcde',3) from lxw_dual

Cde

Hive > selectsubstr ('abcde',-1) from lxw_dual; (same as ORACLE)

E

6. String interception function: substr,substring

Syntax: substr (string A, intstart, int len), substring (string A, intstart, int len)

Return value: string

Description: returns the string A that starts at the start position and has a length of len.

For example:

Hive > select substr ('abcde',3,2) from lxw_dual

Cd

Hive > select substring ('abcde',3,2) from lxw_dual

Cd

Hive > select substring ('abcde',-2,2) from lxw_dual

De

7. String to uppercase function: upper,ucase

Syntax: upper (string A) ucase (string A)

Return value: string

Description: returns the uppercase format of string A

For example:

Hive > select upper ('abSEd') from lxw_dual

ABSED

Hive > select ucase ('abSEd') from lxw_dual

ABSED

8. String to lowercase function: lower,lcase

Syntax: lower (string A) lcase (string A)

Return value: string

Description: returns the lowercase format of string A

For example:

Hive > select lower ('abSEd') from lxw_dual

Absed

Hive > select lcase ('abSEd') from lxw_dual

Absed

9. Unspace function: trim

Syntax: trim (string A)

Return value: string

Description: remove spaces on both sides of the string

For example:

Hive > select trim ('abc') from lxw_dual

Abc

10. Go to the left space function: ltrim

Syntax: ltrim (string A)

Return value: string

Description: remove the space on the left side of the string

For example:

Hive > select ltrim ('abc') from lxw_dual

Abc

11. The right-hand space function: rtrim

Syntax: rtrim (string A)

Return value: string

Description: remove the space on the right side of the string

For example:

Hive > select rtrim ('abc') from lxw_dual

Abc

twelve。 Regular expression replacement function: regexp_replace

Syntax: regexp_replace (string A, string B, string C)

Return value: string

Description: replace the part of the string A that conforms to the java regular expression B with C. Note that escape characters are used in some cases, similar to the regexp_replace function in oracle.

For example:

Hive > select regexp_replace ('foobar',' oo | ar','') from lxw_dual

Fb

13. Regular expression parsing function: regexp_extract

Syntax: regexp_extract (string subject, string pattern, int index)

Return value: string

Description: splits the string subject according to the rules of pattern regular expressions and returns the characters specified by index.

For example:

Hive > select regexp_extract ('foothebar',' foo (. *?) (bar)', 1) fromlxw_dual

The

Hive > select regexp_extract ('foothebar',' foo (. *?) (bar)', 2) fromlxw_dual

Bar

Hive > select regexp_extract ('foothebar',' foo (. *?) (bar)', 0) fromlxw_dual

Foothebar

Note that in some cases escape characters are used, and the equal sign below is escaped with a double vertical bar, which is the rule of java regular expressions.

Select data_field

Regexp_extract (data_field,'.*?bgStart\ = ([^ &] +)', 1) as aaa

Regexp_extract (data_field,'.*?contentLoaded_headStart\ = ([^ &] +)', 1) as bbb

Regexp_extract (data_field,'.*?AppLoad2Req\ = ([^ &] +)', 1) as ccc

From pt_nginx_loginlog_st

Where pt = '2012-03-26'limit 2

14. URL analytic function: parse_url

Syntax: parse_url (string urlString, string partToExtract [, stringkeyToExtract])

Return value: string

Description: returns the specified part of the URL. The valid values of partToExtract are: HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.

For example:

Hive > selectparse_url ('http://facebook.com/path2/p.php?k1=v1&k2=v2#Ref1',' HOST') fromlxw_dual

Facebook.com

Hive > selectparse_url ('http://facebook.com/path2/p.php?k1=v1&k2=v2#Ref1',' QUERY','k1') from lxw_dual

V1

15. Json analytic function: get_json_object

Syntax: get_json_object (string json_string, string path)

Return value: string

Description: parses the string json_string of json and returns the content specified by path. If the json string entered is not valid, NULL is returned.

For example:

Hive > select get_json_object ('{"store":

> {"fruit":\ [{"weight": 8, "type": "apple"}, {"weight": 9, "type": "pear"}]

> "bicycle": {"price": 19.95, "color": "red"}

>}

> "email": "amy@only_for_json_udf_test.net"

> "owner": "amy"

>}

>','$.owner') from lxw_dual

Amy

16. Space string function: space

Syntax: space (int n)

Return value: string

Description: returns a string of length n

For example:

Hive > select space (10) from lxw_dual

Hive > select length (space (10)) from lxw_dual

ten

17. Repeating string function: repeat

Syntax: repeat (string str, int n)

Return value: string

Description: returns the str string repeated n times

For example:

Hive > select repeat ('abc',5) from lxw_dual

Abcabcabcabcabc

18. First character ascii function: ascii

Syntax: ascii (string str)

Return value: int

Description: returns the ascii code of the first character of the string str

For example:

Hive > select ascii ('abcde') from lxw_dual

ninety-seven

19. Left complement function: lpad

Syntax: lpad (string str, int len, string pad)

Return value: string

Description: use pad to complete str to Lenn bit

For example:

Hive > select lpad ('abc',10,'td') from lxw_dual

Tdtdtdtabc

Note: unlike GP,ORACLE, pad cannot default

20. Right complement function: rpad

Syntax: rpad (string str, int len, string pad)

Return value: string

Description: right complement of str to Lenn bit with pad

For example:

Hive > select rpad ('abc',10,'td') from lxw_dual

Abctdtdtdt

21. Split string function: split

Syntax: split (string str, stringpat)

Return value: array

Note: split the str according to the pat string and return the split string array.

For example:

Hive > select split ('abtcdtef','t') from lxw_dual

["ab", "cd", "ef"]

twenty-two。 Collection lookup function: find_in_set

Syntax: find_in_set (string str, string strList)

Return value: int

Description: returns the position where str first appeared in strlist. Strlist is a string separated by commas. If the str character is not found, 0 is returned.

For example:

Hive > select find_in_set ('ab','ef,ab,de') from lxw_dual

two

Hive > select find_in_set ('at','ef,ab,de') from lxw_dual

0

This is the end of the content of "hive string function arrangement". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.