Extracting text from String?

[djcp24]

I want to be able to pull text from this string:

DABLennon,JohnDAQ1466 N Beatles AveDAL

=========================== ===========

Basically I want to be able to pull Lennon, John and put that into a FULL_NAME field & pull 1466 N Beatles Ave and put that into a field for address. Can anyone help me? Or point me in the right direction on how to do this?

I am using SQL Server 2005 on Windows XP

[djcp24]

any ideas guys?

[rmiao]

Can try with charindex and patindex function if string has certain pattern.

[djcp24]

I am new to SQL. How do I use the functions? Is there a video tutorial online that can show me?

[rmiao]

Read books online, has sample code in it.

[Swapnil]

Declare @Str As varchar(500)
Declare @Name As varchar(500)
Declare @Address As varchar(500)

Set @str ='DABLennon,JohnDAQ1466 N Beatles AveDAL'

Select @str = SUBSTRING(@str,CHARINDEX('DA',@str)+3,LEN(@str))
Select @Name = SUBSTRING(@str,1, CHARINDEX('DA',@str)-1)
Select @Name AS FULL_NAME

Select @str = SUBSTRING(@str,CHARINDEX('DA',@str)+3,LEN(@str))
Select @Address = SUBSTRING(@str,1, CHARINDEX('DA',@str)-1)
Select @Address As Address