Extracting part of the text from String

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

General SQL Server Forums

New to SQL Server Programming

Extracting part of the text from String

Author

Topic

Maverick_
Posting Yak Master

107 Posts

Posted - 2010-10-29 : 12:37:49

Hello all,

I have this basic query:
SELECT
ce.enquiry_number,
ce.enquiry_description,
ce.enq_location
es.subject_name
FROM
cent_enq ce,
enquiry_subject es
WHERE
es.service_code = ce.service_code AND
es.subject_code = ce.subject_code

In the ce.enq_location field I records that have some data such as:
"AssetID: 141019940004402300; Feature Location:"
"AssetID: 0; Feature Location:"
Easting,Northing:375364,8814947.

From those example of rows of data, I would like to write a SQL extract for example that takes "AssetID: 141019940004402300;" out of the string and puts it into a new column.

Same applies for easting and northing data from string.

Where there is this AssetID, and easting and northing, the text always begins with those names.

Is there a way of doing this? I have never done it before and am stuck!

Any help would be appreciated!

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-29 : 12:51:50

So the column delimiter is the ; and the data/ label delimiter is a colon?

Any you want to put the data into a column that's created with it's label?

Do you have any key data in this data?

And "Easting, Northing" are different formats than the rest

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

Maverick_
Posting Yak Master

107 Posts

Posted - 2010-10-29 : 13:04:59

Hi Brett

Yes you are right.

Unfortunately there is no key in this data it is a free text field :(

Sometimes the "AssetID" data is there (represented by long digits) but sometimes there isn't and where there isn't its a 0.

Not all enquiries have AssetID or Easting and Northing data so I just want to extract either of those where SQL finds it in the SQL field.

I was able to do it in Excel but can't figure out how to do it in SQL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-01 : 12:17:45

you can use LIKE function and look for patterns "AssetID:" and "Easting, Northing" in your string like


SELECT
ce.enquiry_number,
ce.enquiry_description,
ce.enq_location
es.subject_name
FROM
cent_enq ce,
enquiry_subject es
WHERE
es.service_code = ce.service_code AND
es.subject_code = ce.subject_code
AND (ce.enq_location LIKE 'AssetID:%'
OR  ce.enq_location LIKE 'Easting,Northing:%')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources