0

I have a string like the following in a sqlite 1 column:

1a 2B 3c 354 AfS 151 31s2fef 1fs31 3F1e2s 84f64e 45fs

space separated, x amount of characters 0-9, a-z, A-Z, there might be punctuation I'm not sure, but it is definitely space separated.

I'm trying to make a regular expression so I can query the database by number of words. basically if I wanted to get the 6th "word" in the example I'd be looking for:

151

so I tried to make a regular expression that says if the Nth word = 151, return me that row.

Here's what I've got so far. 

SELECT * FROM table1 WHERE column1 REGEXP ^((?:\S+\s+){1}){6}

That unfortunately gives me the first through sixth words, but I really want to pinpoint the 6th word like the example above.

Also, I was thinking so save room in the database I could get rid of the white space, I'd just need to know how to count a specific number of characters in to the string which I couldn't figure out either.

Thanks for the help, never written a regular expression before.

MetaStack
  • 3,266
  • 4
  • 30
  • 67
  • 1
    How about using a junction table and storing the separate values in the correct format for a relational databases? Long strings with separators in a single column is not the relational way. – Gordon Linoff Nov 23 '15 at 15:21
  • maybe I should, the only reason I haven't is because it seems really complicated with the thing I'm trying to organize. I'm sure a seasoned professional could do it but its hard for me to get my head around. I have a table with input, action and result columns. The input and results are the long, indeterminately strings, and the same action could have multiple results depending on the input. so I'd have to make a relation of relations or something, I'm not sure if I'm up for that. – MetaStack Nov 23 '15 at 15:30
  • 1
    @dasblinkenlight I don't mean return me just the 6 word, what I really mean is return me everything on this row if the 6th word matches what I'm looking for, sorry if that wasn't clear - I just want to match on the 6th word. – MetaStack Nov 23 '15 at 15:35

2 Answers2

1

Use following regex

^([^\s]+\s){6}(.*?)(\s|$)

this must return 151, you can change {6} with your number to match the string.

ogres
  • 3,660
  • 1
  • 17
  • 16
1

If all you need is a match, there is no need for the non-capturing group. Just match a non-space + space group 5 times and follow with a 151.

^(\S+\s+){5}151
sidyll
  • 57,726
  • 14
  • 108
  • 151