How can I extract string ‘abc.com’ from a string http://[email protected] using sql?

Question

How can I extract string ‘abc.com’ from a string http://[email protected] using sql?

Answer 1

in SQL Server, try (assumes no nulls and string is found):

declare @x  varchar(100), @y varchar(100)
select @x='http://[email protected]',@y='abc.com'
print SUBSTRING(@x,CHARINDEX(@y,@x),LEN(@y))

EDIT
based on Dave Carlile answer, these handles the case when @ is not present:

retunrs empty string when "@" is not present

declare @s varchar(100)
select @s = 'http://infoabc.com'
select right(@s, len(@s) - CASE WHEN charindex('@', @s)>0 then charindex('@', @s) ELSE len(@s) END)

returns null when "@" is not present

declare @s varchar(100)
select @s = 'http://infoabc.com'
select CASE WHEN charindex('@', @s)> 0 THEN right(@s, len(@s) - charindex('@', @s)) ELSE NULL END

Answer 2

As Joel said, there is no real answer - it depends on the database you're using.

I assume you're looking for a regular expression, and not extracting the string "abc.com" specifically. I suppose there might be a database which would have a built-in function for regular expressions, but most probably wouldn't. Fortunately, most databases today allow you to incorporate your own code in another language (usually Java or .NET, again - depends on the database).

If your database allows it, you could use this kind of functionality in your SELECT statements.

Answer 3

A more generalized variation to KM's response...

declare @s varchar(100)

select @s = 'http://[email protected]'

select right(@s, len(@s) - charindex('@', @s))

This will locate the '@' in the string and grab everything to the right. This is most likely MSSQL specific syntax.