RaqForum 41 No.
135 View  • 
     ...

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/


 
 •  135 View  •