EXTRACT TEXT FROM AN NTEXT FIELD INTO ANOTHER TABLE BASED ON FIELDNAMES
Posted on 2003-03-02
For those who like a bigger challenge than how to link two tables
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
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)
DECLARE @loop_counter int,
if ISNUMERIC(@thisstring) =1
SET @loop_counter = 1
set @tempstring = ''
WHILE (@loop_counter <= 5)
if isnumeric(substring(@thisstring, @loop_counter ,1)) =1
SET @loop_counter = @loop_counter + 1
--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.