-7
id range differences (inclusive sum of Differences total
1 '01-08,24-32' 8,9 17
2 '38-70' 33 33
3 '01-25, 27-38' 25,12 37
4 '1-6,13-20,25-32' 6,8,8 22
5 '1-4, 7-8, 11-12' 4,2,2 8
4 NULL NULL NULL
5 '145-167, 169-214, 217-218, 247-254, 256-257, 382' 13,46,2,8,2,1 72

I have been given a table which has a range of pages as a string as a field. I need to find the simplest coding method to get the differences (inclusive) of each string range and then sum those differences.

I am using GoogleSQL at work so there is some carry over from MySQL but some functions aren't carries over so I've been struggling. I'm looking for a solution which doesn't have too many functions which will be hard for me to map over to gSQL.

I thought maybe doing some REGEX matching or extracting would be the way to go but I'm not sure how to get the ranges isolated as INTS to do math on them.

TIA

Improve this question
6
  • 1
    What are you having difficylty with and what have you tried? Please read the help center pages on how to write a good question that will attract good answers on this site. Commented Nov 18 at 22:34
  • 1
    This is not something that can be done directly in SQL. You would have to use a stored procedure to loop over the comma-delimited strings. Is it possible for you to normalize so they're in separate rows? Commented Nov 18 at 22:42
  • See stackoverflow.com/questions/17942508/… for example. Commented Nov 18 at 22:43
  • 1
    If you need to di this in google bigquery or spanner, then better ask the question as such as opposed to trying to convert a nysql solution over. Mysql does not have array data tyoe, so would be difficult to implement such logic in mysql. Commented Nov 18 at 23:06
  • This can be done with BigQuery SPLIT to ARRAY and UNNEST methods and GROUP BY. If you find it necessary, please open the question again. I would suggest my option as an answer. Commented 2 days ago
Related questions
4
Regex function to extract numbers from string-type values, separated by "-", to get the difference of each two values
1216
Check whether a string matches a regex in JS
875
How to count string occurrence in string?
Load 5 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.