extract number from a string sql

Question

I have this SQL Data:

AT_SendMail_v1

AT_Certificate_v10

AT_Certificate_v100

And I want this output:

AT_SendMail_v2

AT_Certificate_v11

AT_Certificate_v101

I have this code but it doesn't work for the numbers that they more than 19:

SELECT CASE 
        WHEN ISNUMERIC(RIGHT([internalname],1)) = 1 
        THEN LEFT([internalname],LEN([internalname])-1)  + cast((CONVERT(INT, RIGHT([internalname],1)) + 1) as varchar(100)) 
        ELSE [internalname] + '_v1'
        END
       ,[id]
FROM [task]

thanks :)

Try picking up everything after _v till you reach space and then check it if it is numeric or not and increment it if it is — Farrukh Subhani, May 30 '17 at 11:49
This may help you: https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string — Dani, May 30 '17 at 11:51
Your code worked fine for me... what am I missing? Your 3 test cases result in your 3 expected results. — Jamiec, May 30 '17 at 11:54
Any answer to this is just prolonging the agony you are going to deal with. The real issue here is that you have violated 1NF by sticking two values into the same tuple. You should split version to its own column and this issue suddenly just disappears. — Sean Lange, May 30 '17 at 13:10

score 0 · Answer 1 · answered May 30 '17 at 11:56

You could use SUBSTRING to get number after _v, after it check If It is numeric using ISNUMERIC, after It increase that number by 1, convert It to string and concat string.

DECLARE @internalname NVARCHAR(40)
SET @internalname = 'AT_SendMail_v19'

SELECT 
    CASE WHEN ISNUMERIC(SUBSTRING(@internalname, CHARINDEX('_v', @internalname) + 2, LEN(@internalname))) = 1
         THEN SUBSTRING(@internalname,+2, CHARINDEX('_v',@internalname)) + CAST(SUBSTRING(@internalname, CHARINDEX('_v', @internalname) + 2, LEN(@internalname)) + 1 AS NVARCHAR(60))
         ELSE @internalname + '_v1'
    END

ISNUMERIC can have shortcomings, as it will recognize dollar signs and scientific notation as numbers as well. Try `SELECT ISNUMERIC($)` or `SELECT ISNUMERIC(1e4)` and you'll see what I mean. — Eli, May 30 '17 at 13:50

Keith · Answer 2 · 2017-05-30T13:11:37.597

0

Here is one way to increment the numbers, you can then build it into your string...

DECLARE @str NVARCHAR(20) = 'AT_SendMail_v1';

DECLARE @intCharIndex INT = CHARINDEX('_v',@str);

IF(@intCharIndex = 0)

BEGIN 

    SELECT 'No string found';
END

IF(@intCharIndex > 0)

BEGIN 
    DECLARE @int INT = LEN(@str) - @intCharIndex - 1;

    DECLARE @intVersion INT = RIGHT(@str,@int) + 1;

    SELECT LEFT(@str,@intCharIndex) + 'v' + RTRIM(CAST(@intVersion AS NVARCHAR(4)));
END

edited May 30 '17 at 13:11

answered May 30 '17 at 11:58

Keith

1,008
10
19

It returns only number, also what if string will be provided like: `'AT_SendMail_'? – Stanislovas Kalašnikovas May 30 '17 at 12:01
I state it only returns the number in the code, the point being this can be used to extract the number and increment, this can then be plugged back into the string. Based on the question, there is nothing to suggest that would ever be the case. But, I take your point and have amended it to check if there is an index of what is being looked for so it would not error. – Keith May 30 '17 at 13:06

Ilyes · Answer 3 · 2017-05-30T12:18:27.893

0

Try this :

declare @test varchar(50) = 'AT_SendMail_v1';

select Output = case when ISNUMERIC( substring ( @test , charindex('_v',@test)+2 , len(@test)) )=1 then

         left (@test , charindex('_v',@test)+1) + cast( cast(substring ( @test , charindex('_v',@test)+2 , len(@test)) as int)+1 as varchar )
         else
         @test+'_v1' end;

Demo.

edited May 30 '17 at 12:18

answered May 30 '17 at 12:12

Ilyes

14,640
4
29
55

It will fail if value will be like `'AT_SendMail'` – Stanislovas Kalašnikovas May 30 '17 at 12:15
ISNUMERIC can have shortcomings, as it will recognize dollar signs and scientific notation as numbers as well. Try SELECT ISNUMERIC($) or SELECT ISNUMERIC(1e4) and you'll see what I mean. – Eli May 30 '17 at 13:50

score 0 · Answer 4 · edited May 30 '17 at 13:53

that was the best code for me, thanks :)

SELECT 
    CASE 
        WHEN CHARINDEX('_v', [internalname], 1) <> 0 THEN LEFT([internalname], CHARINDEX('_v', [internalname], 1) + 1) + CAST(CAST(RIGHT([internalname], LEN([internalname]) - CHARINDEX('_v', [internalname], 1) - 1) AS INT) + 1 AS VARCHAR(100)) 
        ELSE [internalname] + '_v1' 
    END 
    ,[id] 
FROM [task]

SqlZim · Accepted Answer · 2017-05-31T12:57:36.840

Assuming that the number is always the end of the string:

using patindex():

select 
    name
  , new_name = case when patindex('%[_]v[1234567890]%',name) > 0
      then left(name,(patindex('%[_]v[1234567890]%',name)+1))
         + convert(varchar(10),convert(int
           ,right(name,len(name)-(patindex('%[_]v[1234567890]%',name)+1)))
           +1)
       else name+'_v1'
    end
from task

rextester demo: http://rextester.com/AWPXB86451

returns:

+----------------------+----------------------+
|         name         |       new_name       |
+----------------------+----------------------+
| AT_SendMail_v1       | AT_SendMail_v2       |
| AT_Certificate_v10   | AT_Certificate_v11   |
| AT_Certificate_v_v50 | AT_Certificate_v_v51 |
+----------------------+----------------------+

score -1 · Answer 6 · answered May 30 '17 at 12:00

How about this that caters for ANY number after the _v

Select Left(internalName, Len(internalName) - CharIndex('v_', Reverse(internalName)) + 1) + Cast((Cast((Right(internalName, CharIndex('v_', Reverse(internalName)) -1)) As Integer) + 1) As NVarchar(32))
From task

extract number from a string sql

6 Answers6