Dividing a string into two parts and selecting into two variables

I am using SQL Server 2005. I hav开发者_StackOverflow中文版e a string with a special character : like:

XYZ:xyz@mail.com

I want to divide this string into two sub-strings and select into variables. The special character : is just a separator.

@variable1=XYZ
@variable2=xyz@mail.com

Please guide me on how it is possible.

If ':' is missing, @v1 will be null

DECLARE @v1 varchar(20) 
DECLARE @v2 varchar(20)

DECLARE @s varchar(20)
SET @s = 'XYZ:xyz@mail.com'

SELECT @v1 = stuff(b, len(b),1,''), 
       @v2 = stuff(@s, 1, len(b),'') 
FROM (SELECT left(@s, charindex(':', @s)) b) a

SELECT @v1 v1,@v2 v2

Result:

v1  v2
--- ------------
XYZ xyz@mail.com

String Dividing Guide:

1. Use CHARINDEX to find the position of :.

2. Use SUBSTRING to grab all the characters before the position of :, and put them into @variable1.

3. Use SUBSTRING to grab all the characters after the position of :, and store them into @variable2.

A possible implementation:

DECLARE @string varchar(max), @variable1 varchar(max), @variable2 varchar(max);
SET @string = 'XYZ:xyz@mail.com';

SELECT
  @variable1 = SUBSTRING(S, 1, P - 1),
  @variable2 = SUBSTRING(S, P + 1, L - P)
FROM (
  SELECT
    S = @string,
    P = CHARINDEX(':', @string),
    L = LEN(@string)
) s;

SELECT @variable1, @variable2;

Output:

-------------------- --------------------
XYZ                  xyz@mail.com

DECLARE @var VARCHAR(100)
SET @var='XYZ:xyz@mail.com'

SELECT 
SUBSTRING(@var, 1, CHARINDEX(':',@var)-1) as var1 -- get part till :
,SUBSTRING(@var, CHARINDEX(':',@var)+1, LEN(@var)) AS var2 -- get part after :

Explained:

substring get's a part of a string from start_position to end_position

SUBSTRING( string, start_position, end_position)

charindex get's the position of a character inside a string

CHARINDEX( character_to_search, string_to_search_in)

http://msdn.microsoft.com/en-us/library/ms187748.aspx

http://msdn.microsoft.com/en-us/library/ms186323.aspx