Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

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

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
jessesmlewis
Asked:
jessesmlewis
  • 4
  • 4
  • 2
  • +2
2 Solutions
 
Anthony PerkinsCommented:
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
 
jessesmlewisAuthor Commented:
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
 
Anthony PerkinsCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
routledgeCommented:
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
 
jessesmlewisAuthor Commented:
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
 
routledgeCommented:
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
 
jessesmlewisAuthor Commented:
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
 
jessesmlewisAuthor Commented:
anybody else got any good ideas on comments on my spelling please comment
0
 
Anthony PerkinsCommented:
>>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
 
CleanupPingCommented:
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
 
Anthony PerkinsCommented:
Suggest splitting the points between all contributors.

Anthony
0
 
monosodiumgCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now