Extracting data from text field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Extracting data from text field

Author

Topic

USQL
Starting Member

2 Posts

Posted - 2005-07-06 : 23:07:06

Hello,

I am some what new to T-SQL and am trying to learn as I go.
I need assistance with extracting some text from a field in a SQL Server 2000 database. (I have searched the forums but did not find my specific question already posted).

-The column name is 'value'
-Each field in the column contains text like this all togther:
Name=Joe Address=123 Smith Email=joe@smith.com

-Some fields are missing the address or name fields(Therefore, the email field is not always at the same position). I have been working all day to come up with a script using substrings as a way to find the 'Email=' text and extract the whole email address after it, i.e. 'joe@smith.com' and place the email address into a new column.

I appreciate any help offered as this is quite difficult for a new user of t-sql. Please let me know if more information is required.

Thanks in advace,
-Sean

raclede
Posting Yak Master

180 Posts

Posted - 2005-07-06 : 23:35:47

can you provide the table structure and sample data.

USQL
Starting Member

2 Posts

Posted - 2005-07-07 : 00:16:34

raclede,

I'm not quite sure what other 'table structure' info you need, but the table is named 'registration' and the 'value' column is limited to 255 text characters. There are also some other columns with user id and that's about it. Here is some sample data:

Value(Column Name)
Email=north@abc.com Score=50
Name=Jeff Lend Email=south@xyz.com Score=25

There is a double space between each category, all in 1 column.
My goal: To extract just the email address into another table or column like this:

Email Addresses(Column Name)
north@abc.com
south@xyz.com

Thanks for you help,
-Sean

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-07 : 02:13:13

You need to make use of charindex and substring functions
Not sure whether this works for all your data

declare @s varchar(100)
select  @s='value=34598 Email=north@abc.com Score=50'
select substring(@s,charindex('Email=',@s)+6,charindex('com',@s)-charindex('Email=',@s)-2)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources