...

SQL, split up a field according to whether the specified characters are unique or not

 

The table named Table in SQL Server has a word field:

AAA

AAB

AAC

ABA

ACA

ACB

BAA

BAB

BAC

BBA

BCA

BCB

BDA

AAA

We need to split up the word field into three fields. The 1st field is the 1st character of the original field. If the original field’s first two characters are unique in the field, such as ABA, the 2nd field will be the original field and the 3rd field is null. If the first two characters are not unique, then the 2nd field is these two characters and the 3rd field is the original field.

A

AA

AAA

A

AA

AAB

A

AA

AAC

A

ABA

null

A

AC

ACA

A

AC

ACB

B

BA

BAA

B

BA

BAB

B

BA

BAC

B

BBA

null

B

BC

BCA

B

BC

BCB

B

BDA

null

A

AA

AAA

Here is the SPL code:

A

1

=mySQLDB.query("select word from Table")

2

=A1.new(left(word,1),word,null)

3

>A2.group(left(#2,2)).run(if(~.len()>1,~.run(#2=(left(#3=#2,2)))))

4

return A2

The new()function creates a new two-dimensional table. group() function groups rows and retains the grouping result. run() function loops to modify values and return the result; ~ is the current group and #2 represents the 2nd field.

Source:https://www.reddit.com/r/SQL/comments/1c82d1a/complex_multicolumn_string_manipulation_query/


 
 •  64 View  •