<p>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:</p>

<p><code>activation successful of id 1010101</code></p>

<p>The line above is the exact structure of the data in the db field.</p>

<p>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 ;-)</p>

```
```
-- 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
```
.