0

So I have a column with strings that are multiples of 5 characters, such as "12345" or "abcde12345" or "asdfghjkli12345". What I'm trying to do is write a query to split each of these strings into 5 character chunks and return the distinct ones.

So with "12345" , "abcde12345" , "asdfghjkli12345"

I would get back "12345" "abcde" "asdfg" and "hjkli"

Is this possible?

TheEwook
  • 11,037
  • 6
  • 36
  • 55
Igglyboo
  • 837
  • 8
  • 21

2 Answers2

0

MySQL does not include a function to split a delimited string. Although separated data would normally be split into separate fields within a relation data, spliting such can be useful either during initial data load/validation or where such data is held in a text field.

The following formula can be used to extract the Nth item in a delimited list, in this case the 3rd item "ccccc" in the example comma separated list.

select replace(substring(substring_index('aaa,bbbb,ccccc', ',', 3), length(substring_index('aaa,bbbb,ccccc', ',', 3 - 1)) + 1), ',', '') ITEM3

The above formula does not need the first item to be handled as a special case and returns empty strings correctly when the item count is less than the position requested.

You can also create your own split function and use it. Split value from one field to two

Source: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

Community
  • 1
  • 1
TheEwook
  • 11,037
  • 6
  • 36
  • 55
-1

This sounds like it would be outside the limitations of SQL and would certainly require a programming language. It would be VERY easy in any 3GL.

SS781
  • 2,507
  • 2
  • 14
  • 17