Incrementing a substring
-
Hi,
I am currently trying to increment the highest value of a substring in a column. The strings are in the format
L* - N*[L]
where each L denotes a letter and each N an integer. The [L] in brackets is optional. I search for certain records using the letter prefix and then increment the NNNN part, but I want to be able to ignore any records that have the optional [L].
I have got as far as stripping off the text prefix at the start, but keep coming into problems when trying to find the optional [L]. Is there a function where I can check if something is an integer or not? I was thinking about checking the ASCII value...but surely there must be a simpler way?
-
If what you need is to check only the final character to see if it's numeric, you could do something like this:
if (right(@var_or_column_here, 1) like '[0 - 9]') ...do stuff...
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
Still having problems with another part of it, but that was very helpful. Thank you Mia
-
No worries! If you can't solve your other problem, it may be worth posting a code snippet to see if anyone can help.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
does ISNUMERIC work for you???
-
Doh! Of course - why do it your own long-winded way when there is a T-SQL function?!
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
SET Col = STUFF(Col,CHARINDEX('-',Col)+2,CHARINDEX(' ',REVERSE(Col))-1,RIGHT(Col,CHARINDEX(' ',REVERSE(Col)))+1)
WHERE LEFT(Col,1) = 'F' AND ISNUMERIC(RIGHT(Col,1)) = 1--Jonathan
--Jonathan -
can you SEPARATE The Three components into COLUMNS. It is going to me ALOT easier to maintain, simpler to query against an probably Faster!!
HTH
* Noel
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply