SQL strip text and convert to integer

Question

Hi all,

In my database (SQL 2005) I have a field which holds a comment but in the comment I have an id and I would like to strip out just the id, and IF possible convert it to an int:

'activation successful of id 1010101'

The line above is the exact structure of the data in the db field.

And no I don't want to do this in the code of the application, I actually don't want to touch it, just in case you were wondering ;-)

Any help would be much appreciated!

Thanks!

Answer 1

Take a look at my accepted answer in this thread, I think it does what you want:

http://stackoverflow.com/questions/52315/t-sql-trim-nbsp-and-other-non-alphanumeric-characters#52327

Answer 2

-- Test table, you will probably use some query
DECLARE @testTable TABLE(comment VARCHAR(255))
INSERT INTO @testTable(comment)
VALUES ('activation successful of id 1010101')

-- Use Charindex to find "id " then isolate the numeric part
-- Finally check to make sure the number is numeric before converting
SELECT CASE WHEN ISNUMERIC(JUSTNUMBER)=1 THEN CAST(JUSTNUMBER AS INTEGER) ELSE -1 END
FROM (
select right(comment, len(comment) - charindex('id ', comment)-2) as justnumber
from @testtable) TT

I would also add that this approach is more set based and hence more efficient for a bunch of data values. But it is super easy to do it just for one value as a variable. Instead of using the column comment you can use a variable like @chvComment.

Answer 3

I don't have a means to test it at the moment, but:

select convert(int, substring(fieldName, len('activation successful of id '), len(fieldName) - len('activation successful of id '))) from tableName

Answer 4

This should do the trick:

SELECT SUBSTRING(column, PATINDEX('%[0-9]%', column), 999)
FROM table

Based on your sample data, this that there is only one occurence of an integer in the string and that it is at the end.

Answer 5

If the comment string is EXACTLY like that you can use replace.

select replace(comment_col, 'activation successful of id ', '') as id from ....

It almost certainly won't be though - what about unsuccessful Activations? You might end up with nested replace statements

select replace(replace(comment_col, 'activation not successful of id ', ''), 'activation successful of id ', '') as id from ....

[sorry can't tell from this edit screen if that's entirely valid sql]

That starts to get messy; you might consider creating a function and putting the replace statements in that.

If this is a one off job, it won't really matter. You could also use a regex, but that's quite slow (and in any case mean you now have 2 problems).

Answer 6

Would you be open to writing a bit of code? One option, create a CLR User Defined function, then use Regex. You can find more details here. This will handle complex strings.

If your above line is always formatted as 'activation successful of id #######', with your number at the end of the field, then:

declare @myColumn varchar(100)
set @myColumn = 'activation successful of id 1010102'


SELECT
 @myColumn as [OriginalColumn]
, CONVERT(int, REVERSE(LEFT(REVERSE(@myColumn), CHARINDEX(' ', REVERSE(@myColumn))))) as [DesiredColumn]

Will give you:

OriginalColumn                           DesiredColumn
---------------------------------------- -------------
activation successful of id 1010102      1010102

(1 row(s) affected)

Answer 7

CAST(REVERSE(LEFT(REVERSE(@Test),CHARINDEX(' ',REVERSE(@Test))-1)) AS INTEGER)

Answer 8

select cast(right(column_name,charindex(' ',reverse(column_name))) as int)