?
Solved

EXTRACT TEXT FROM AN NTEXT FIELD INTO ANOTHER TABLE BASED ON FIELDNAMES

Posted on 2003-03-02
13
Medium Priority
?
398 Views
Last Modified: 2011-09-20
For those who like a bigger challenge than how to link two tables

History first
There is a company in England that sells washing machines, cookers, dishwashers etc,
They scan from books, cats, and cut and past from internet sites the info they need to help them and the
Customer make an informed choices, the info as you can image is all over the place height, width, depth and much more.

They are currently using dbase tables with dbase write progs and all is well.
But things have to move on so IV started the process of moving to mssql 2000 on server 2000 Most of the stuff I have to do is easy no real hardship but the first big question I had is how to move this function.

I don’t what a literal copy, I would like to change the way the dbase code works, each time the query is run it sets an index range based on group(dish,washing,frezzing) and then searches the remaining data around 2,000 records the search takes say 2 seconds but for the future I would like to take advantage of triggers to copy the data on an insert, update to a separate table, but thats not all, what data should be copied, at this time the new tables "details" could have fields height, width, depth and extracting the data and placing it in the table "details" even at this point would not be that hard, now here’s the  (thing), I would like to know what data to extract based on the field names of the table "details" so if the table has the fields names height, width, depth then the function would look-in the notes field for "depth=", 'width=", "height=" the reason for this is that if the extraction is done within a loop  say while len(fieldname)>0 begin ---end just by adding a field name say color, or energyrating you can extract more data into new fields without the need to reprogram, there are about 10 updates,5 inserts per day so im not going to worry that doing things this way will be some what slower than others im more over trying to find new ways not to have to reprogram for small changes

there is a stock table with prim field partno varchar(15), instock int, bin varchar(10) ......etc
there is a notes table with prim field partno varchar(15), notes ntext -->(the field we are searching)

the is a relation between the stock table and the notes table based on prim key partno

heres a quary that i have played with in the past

SELECT     *
FROM     stock
WHERE     (dbo.SD(PATINDEX('%depth=%', NOTES)) < 600)
AND      (dbo.SD(PATINDEX('%width=%', NOTES)) < 600)
AND      (dbo.SD(PATINDEX('%height=%',NOTES)) < 600)

hers a function iv played with (but not fully tested)

CREATE FUNCTION [dbo].[SD] (@thisstring varchar(5) )
RETURNS  varchar (5)
AS  
BEGIN
DECLARE @loop_counter int,
         @tempstring varchar(5)
if ISNUMERIC(@thisstring) =1
begin
     return (@thisstring)
end --begin
SET  @loop_counter = 1
set @tempstring = ''
WHILE (@loop_counter <= 5)
   BEGIN
      if isnumeric(substring(@thisstring, @loop_counter ,1)) =1
     begin
          set @tempstring=@tempstring+substring(@thisstring,@loop_counter,1)
     end
      SET @loop_counter = @loop_counter + 1
end --begin
RETURN (@tempstring)
END

--to select the fields from the "details" table

select sysobjects.name as view_name, syscolumns.name as column_name
from  sysobjects left join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.xtype='U' and sysobjects.name='details' and  syscolumns.name <> 'partno'

-- yes i got the code from a previus question posted on this site


im curios to see what you think and see if somebody has some fab way to program this, each time iv tried the code just looks like badly written dbase and I know that’s not the way to go.


0
Comment
Question by:jessesmlewis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +2
13 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
ID: 8051816
Without going into any depth (no time), it seems this would be handled a lot better (simpler and better performance) with Full Text Search.

You could then do something like (untested):
SELECT     *
FROM     stock
WHERE    Contains(Notes,'"depth=" Or "width=" Or "height="')

Anthony
0
 

Author Comment

by:jessesmlewis
ID: 8054093
HERES SOME DATA REAL DATA
"BAUMATIC" (BROWN) 600mm  SINGLE FAN OVEN WITH VARIABLE GRILL DOUBLE GLAZED DOOR WITH TOWEL RAIL TYPE HANDLE, INTERIOR LIGHT, MINUTE MINDER, HEIGHT=580mm. WIDTH=596mm. DEPTH=524mm   (13 AMP SUPPLY)  

WHAT IM TRYING TO EXTRACT INTO THE "DETAILS" TABLE IS
580 INTO THE HEIGHT FIELD
596 INTO THE WIDTH FIELD
524 INTO THE DEPTH FIELD

SO THAT YOU CAN THEN query
SELECT *
FROM STOCK
WHERE DEPTH =<600 AND WIDTH <=600 AND HEIGHT <=800

? USEING THIS HOW WOULD YOU CHECK I
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8054515
I see your point and it is going to be tough. One approach would be to extract with Full Text Search all the rows that include "depth", "height" and "width" and insert into a temporary table for further processing similar to the code you posted.

Sorry I could not be more help,
Anthony
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 2

Assisted Solution

by:routledge
routledge earned 100 total points
ID: 8055957
I have done exactly this for a self-catering property rental site where we consolidate information from different tour operators and load into an SQL Database. We built some fundamental routines to search for sentences containg a supplied keyword (in this case it might be 'height' - in our case it might be 'beach') and then find the nearest distance unit 'mm' for you, 'km' or 'miles' etc. for us and convert to a nominated output field. This can then be used in a Query or in a trigger to populate the derived columns on insert of the new data.

The update syntax for a column in the trigger becomes (for the distance to the nearest beach):

dbo.tblProperties.BeachDistance =
dbo.fnGetAttributeDistance(
dbo.tblProperties.Summary,
dbo.tblProperties.Comments,
dbo.tblProperties.HowToFindIt,
dbo.tblProperties.BriefDescription,
dbo.tblProperties.Location,
'beach')


The function takes a series of NTEXT columns and performs the search on each of them in turn to find a match for the keyword being used.

Once you have built this you can use the same function for as many target columns as you wish, and even collect the target columns from a separate table if you wish. In our case, we also find Metres, Yards, KM etc. and convert to a standardised distance for comparison.

This is a fair  bit of code to post on here and fairly specific in terms of units etc. but I can tell you it DOES work, we extract some 15 or so "distances" in this way for each property that gets inserted in the table and when I added 500 or so yesterday it took just a few seconds to run. Since it is generally a one off (on insert or amend) it seems quite manageable.

Once you have used this approach, then you can very easily run queries on the "processed" table without having to resot to complext pattern searches.

If this sounds interesting I can tell you more if you wish.

Good luck,

Alan
0
 

Author Comment

by:jessesmlewis
ID: 8056216
ALAN
COULD BE WHAT IM LOOKING FOR DOES IT HAVE THE ABILITY TO EXTRACT THE DATA BASED UPON FILED NAMES?
PLEASE EITHER POST HERE OR IF YOU ASK I CAN PUBLISH HERE MY PUBLIC EMAIL ADDRESS
0
 
LVL 2

Expert Comment

by:routledge
ID: 8056681
Not sure what you mean by "filed names" - or do you mean "field names"??? I guess the latter. It does not do this now, but I guess it would be easily modified to do that. It is built as a series of functions that have columns or field passed in so the calling structure (an sp or similar) could do that. I am not sure about the protocol of this place, but if you do post your email address we can perhaps communicate directly.
0
 

Author Comment

by:jessesmlewis
ID: 8057037
programer and english teacher to "filed names" yes field names thanks im not sure about protocol but lets risk all
jessesmlewis@yahoo.co.uk
0
 

Author Comment

by:jessesmlewis
ID: 8057051
anybody else got any good ideas on comments on my spelling please comment
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8059504
>>anybody else got any good ideas on comments on my spelling please comment << Spelling is OK, except for the ocassional lapse into all caps <g>

Anthony
0
 

Expert Comment

by:CleanupPing
ID: 9276214
jessesmlewis:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9313478
Suggest splitting the points between all contributors.

Anthony
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 10994190
No comment has been added to this question in more than 239 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Split: acperkins http:#8051816 and routledge http:#8055957

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question