Last post Nov 15, 2016 04:27 PM by oned_gk
Nov 15, 2016 03:39 PM|rpfinnimore|LINK
I have a table with a column containing strings each of which has a number embedded in it...
etc... from which I want to extract the highest numeric value from each occurrence of the string portion.
So for the 'boa' string I want to return 003, from 'xkar' I want to return 002 and from 'ab' I want to extract 001.
Could someone kindly show me how I might do this?
Thanks tonnes for any help, Roscoe
Nov 15, 2016 03:57 PM|PatriceSc|LINK
And this is always the last 3 characters ?
If seems something such as (untested) :
GROUP BY LEFT(YourColumn,LEN(YourColumn)-3)
might do the job that is it is grouping on all characters except the 3 last one and taking the max value for the last 3 characters.
Also it usually means that you are actually storing two information into a single column. It is likely easier/more efficient to use two separate columns for storage even if you concat/split them when viewed/edited by the user rather than to use a single
column and having to split them for processing.
Nov 15, 2016 04:06 PM|rpfinnimore|LINK
Hi Patrice, Thanks for the reply. No does not always have to be the last three characters, could be of form xkay1 or xkay10001.
Point taken about combining columns...I would not have done it this way it was presented to me
Nov 15, 2016 04:17 PM|PatriceSc|LINK
But you won't have something such as x3kay2 or xkay002a ? So it would be from the first digit position to the end of string? And you'll also always have at least one digit at the end ?
You could liekly use then
https://technet.microsoft.com/en-us/library/ms188395(v=sql.110).aspx to locate the first 0 to 9 digit. Try first to see if PATINDEX('%[0-9]%',YourColumn) returns the position for the first digit and then start from that to built your final expression.
The principle will be similar ie you'll have to use string functions found at
https://technet.microsoft.com/en-us/library/ms181984(v=sql.110).aspx to separate the two pieces of information you have in this column according to your storage rules.
Nov 15, 2016 04:27 PM|oned_gk|LINK
select SUBSTRING(COL, PATINDEX('%[0-9]%', COL), LEN(COL))