Extracting substring of query results

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 2005 Forums

Transact-SQL (2005)

Extracting substring of query results

Author

Topic

rabisco
Starting Member

15 Posts

Posted - 2014-07-01 : 12:43:56

The following query returns all documents which contain the url www.agilent.com ...


select distinct docid 
from document
where bodytext like '%www.agilent.com%'

Since the bodytext column is pretty big, I also need to extract the specific string "www.agilent.com" .

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-01 : 12:51:33

Could you show us some examples? I assume you want the full url and not just that specific string since you already know it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

rabisco
Starting Member

15 Posts

Posted - 2014-07-01 : 14:41:01

Below is an example of data in the bodytext column



Agilent Technologies Inc. (NYSE: A) and Freescale Semiconductor today announced results from a collaborative effort to expedite the development of ZigBee solutions. Agilent has enhanced the user-friendliness of its N4010A Wireless Connectivity Test Set, while Freescale has successfully streamlined the development and qualification of its newest ZigBee chipsets. 
For More Information
Information about Agilent's N4010A Wireless Connectivity Test Set is now available atwww.agilent.com/find/n4010a. Information regarding Agilent's 89601A Vector Signal Analyzer and 93000 Test Systems is available at www.agilent.com/find/89600 andwww.agilent.com/find/ate, respectively. Photos of the products are available atwww.agilent.com/find/ZIGBJR_image.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-01 : 14:50:25

Given that example, what do you need to extract? Please also show us a few more examples with expected output.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-01 : 14:53:11

Please read through this for how to post the question/sample data/expected output: http://www.sqlservercentral.com/articles/Best+Practices/61537/.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

rabisco
Starting Member

15 Posts

Posted - 2014-07-01 : 15:06:42

quote:
Originally posted by tkizer

Given that example, what do you need to extract? Please also show us a few more examples with expected output.

A few of the examples of the output are;

1. www.agilent.com/find/89600
2. www.agilent.com/find/ate
3. www.agilent.com/find/ZIGBJR_image.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources