Extract the second word from a row using only sql string operations

Help Activities
,
1

hello ,
I need to extract the second word from a row,(please look at the below example- 1 which consists of 3 or 4 or 5 letter words) i got the result using the below expression… but now i found a different case (look at example -2 which have rows with only 2 words) which am getting error,
Exmaple -1
coloumnA
1234567890 1qwe234rq aaa bold
9087654321 abc1234 gold
8901234567 acs1234 ab leee good
expression used -

left (RIGHT([coloumnA],LEN([coloumnA])-11), (INSTR(RIGHT([coloumnA],LEN([coloumnA])-11),’ '))-1 )

example 2
1234567890 1qwe234rq aaa bold
9087654321 abc1234
8901234567 acs1234 ab leee good
3456789012 asdfg12

note - length may vary for first word too

thanks in advance

2

Hi @Dinesh_Anumolu

The best way to extract the string from this is using regex pattern

Use matches activitiy for this by providing the following input

input text: row (ColumnA).ToString

Regex : “(?<=\n).*(?=\n)”

output : results ( which is a collection )

use results.ElementAt(0).ToString to get the value

This will.give the second line occuring value.

Mark it as solution if it resolves ur query

Regards

Nived N :robot:

Happy Automation

1 Like
3

use regex.matches method with pattern string : “(?m)^\w+\s(\w+)\b”.

if source string is
“234567890 1qwe234rq aaa bold
9087654321 abc1234
8901234567 acs1234 ab leee good
3456789012 asdfg12”

source string’s variable : src

target array variable : arr

arr = Regex.Matches(src,"(?m)^\w+\s(\w+)\b").Cast(Of Match).
         Select(Function(m) m.Groups(1).Value).ToArray

the result (arr)
1qwe234rq
abc1234
acs1234
asdfg12

try it.

4

Hello Dinesh, You can do one thing

Use yourString.Split(Environment.NewLine.TocharArray) to split it based on each new line. Then select the item by analyzing in for each and extract the second word for each item by
Split(item.ToString," ")(1)