Things on this page are fragmentary and immature notes/thoughts of the author. Please read with your own judgement!

split¶

SELECT
    A.state,
    split.A.value('.', 'VARCHAR(100)') AS String
FROM (
    SELECT 
        state,  
        CAST('<M>' + REPLACE(city, ',', '</M><M>') + '</M>' AS XML) AS string  
    FROM
        TableA
    ) AS A
CROSS APPLY String.nodes ('/M') AS split(a)

Things on this page are fragmentary and immature notes/thoughts of the author. Please read with your own judgement!

Tips and Traps¶

1. string is a primitive type in Golang, which means a string value has no methods on it but instead you have to use built-in functions (e.g., len) or functions in other modules (e.g., the strings

Tips and Traps¶

1. You can use the split function to split a delimited string into an array. It is suggested that removing trailing separators before you apply the split function. Please refer to the split section before for more detailed discussions.

2. Some string functions (e.g., right, etc.) are available in the Spark SQL APIs but not available as Spark DataFrame APIs.

https://docs.python.org/3/library/stdtypes.html#textseq

1. Since the str class is immutable in Python, no method of the str class is in-place. Instead, all methods of the str class returns a new copy of string.

2. \ needs to be escaped (i.e., use \\) in triple quotes.

The in Operator¶

There is no method named contains in the str class. You can either use the in

https://docs.python.org/3/library/stdtypes.html#textseq

https://www.techbeamers.com/python-format-string-list-dict/

https://www.techbeamers.com/python-format-string-list-dict/#format-a-python-string

str.format¶

An exception will be thrown if the value for a key in the string is not specified, which means that you must specify values for all keys. However, it is OK if you specify values for keys that do not exist in the string. That is you can specify values for more keys than needed.