tags:

views:

1575

answers:

8

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!

A: 

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

Espo
A: 

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

Cervo
A: 

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
Matt Blaine
+1  A: 

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.

njreed.myopenid.com
thanks worked like a charm!
Symbioxys
A: 

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

A: 

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)
Rick Glos
A: 

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

Dan Roberts
A: 

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