July 17, 2015 at 4:24 am
Dear All,
I would like your help in solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.
"2 8 7"
"2 8"
"2"
"22"
"3 2 8"
I have tried the following
Patindex('%[2]% [^0-9] [^1] [^3] [^4] [^5] [^6] [^7] [^8] [^9]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col3,
Patindex('[2]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col4,
Patindex('%[2][^0-9]%',Replace(Marketing_Special_Attributes,'^',' ')),
All help is appreciated
July 17, 2015 at 4:48 am
mohammed.mazahr (7/17/2015)
Dear All,I would like your help in solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.
"2 8 7"
"2 8"
"2"
"22"
"3 2 8"
I have tried the following
Patindex('%[2]% [^0-9] [^1] [^3] [^4] [^5] [^6] [^7] [^8] [^9]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col3,
Patindex('[2]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col4,
Patindex('%[2][^0-9]%',Replace(Marketing_Special_Attributes,'^',' ')),
All help is appreciated
I suggest that you split the string (space delimiter) and process the results. Should be trivial after splitting.
July 17, 2015 at 5:06 am
Thanks any pointers on how to split by multiple space delimiters
July 17, 2015 at 5:17 am
mohammed.mazahr (7/17/2015)
Thanks any pointers on how to split by multiple space delimiters
Give me some examples please.
July 17, 2015 at 5:42 am
Thank you taking a look.
The data i have in the following format
From which i have to extract or split out the value number 2 and ignore 22 and the remaining values.
"2 8 7"
"2 8"
"2"
"22"
"33 2 8 7"
So Split out would be 4 rows and the row with the value 22 would be ignored.
"2"
"2"
"2"
"2"
Once again thank you.
July 17, 2015 at 6:16 am
PATINDEX('% [2] %', ' ' + Marketing_Special_Attributes + ' ')
will give you char position in the string
or zero if not found
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply