declare @.mensagem varchar(8000)
CREATE TABLE #Mensagem (mensagem text)
set @.mensagem = 'Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa dsfsdfsdf sdfsdfsdf 111111111 00 '
insert into #Mensagem select @.mensagem
select * from #MensagemHi Frank.
I ran that under SQL2KEE & it produced a perfect result..
The Query Analyser truncates column output to 256 characters by default, so
try setting your "Maximum characters per column" to something higher (eg
8000) under Query Analyser's "Tools/Options" menu, "Results" tab.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:#ZTWHAgjDHA.2592@.TK2MSFTNGP10.phx.gbl...
>
>
> declare @.mensagem varchar(8000)
> CREATE TABLE #Mensagem (mensagem text)
> set @.mensagem =>
'Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
>
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa dsfsdfsdf sdfsdfsdf 111111111 00 '
> insert into #Mensagem select @.mensagem
> select * from #Mensagem
>
Showing posts with label text. Show all posts
Showing posts with label text. Show all posts
Thursday, March 29, 2012
field in an embedded sql
I added full text indexing on the title field in a table but when i reference
the same field in an embedded sql it says cannot use contain on a field that
is not full text indexed.
example
SELECT top 400 s.story_id, u.title, s.title AS story_name, u.state,
CONVERT(char(10), u.air_date, 101) AS rundown_date,
'' AS video, '' AS cg_text, SUBSTRING(s.text, 1,500) AS script, SUBSTRING(i.
text, 1, 500) AS item_text, i.type, i.content_status, k.keyword, i.
editorial_description AS description, d.description AS notes,
s.editor AS creator, i.original_material_id AS clipname, i.ar_material_id AS
material_id
FROM
(
SELECT NULL AS state, NULL AS type, p.rundown_id, p.ncs_rundown_id, p.
edit_duration, p.title,
CONVERT(char(10), p.air_date, 101) AS air_date, SUBSTRING(CONVERT(varchar(10),
p.edit_start_time, 114), 1, 8) AS edit_start_time
FROM dbo.na_rundown_tbl p
WHERE (rundown_id NOT IN (SELECT ref1 FROM req_state_tbl WHERE (type = 401)))
) AS u
INNER JOIN dbo.na_story_tbl AS s ON s.rundown_id = u.rundown_id
LEFT OUTER JOIN dbo.na_item_tbl AS i ON s.story_id = i.story_id
LEFT OUTER JOIN dbo.na_itemkeyword_tbl AS k ON i.item_id = k.item_id
LEFT OUTER JOIN dbo.na_itemdesc_tbl AS d ON i.item_id = d.item_id where
contains (u.title ,'%midlothian%')
error message received:
Msg 7601, Level 16, State 3, Line 1
Cannot use a CONTAINS or FREETEXT predicate on column 'title' because it is
not full-text indexed.
what does sp_help_fulltext_tables 'catalogname','title' return?
make sure you replace catalogname with the name of your catalog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ARPREET" <u31535@.uwe> wrote in message news:6d68cc930bce5@.uwe...
>I added full text indexing on the title field in a table but when i
>reference
> the same field in an embedded sql it says cannot use contain on a field
> that
> is not full text indexed.
> example
> SELECT top 400 s.story_id, u.title, s.title AS story_name, u.state,
> CONVERT(char(10), u.air_date, 101) AS rundown_date,
> '' AS video, '' AS cg_text, SUBSTRING(s.text, 1,500) AS script,
> SUBSTRING(i.
> text, 1, 500) AS item_text, i.type, i.content_status, k.keyword, i.
> editorial_description AS description, d.description AS notes,
> s.editor AS creator, i.original_material_id AS clipname, i.ar_material_id
> AS
> material_id
> FROM
> (
> SELECT NULL AS state, NULL AS type, p.rundown_id, p.ncs_rundown_id, p.
> edit_duration, p.title,
> CONVERT(char(10), p.air_date, 101) AS air_date,
> SUBSTRING(CONVERT(varchar(10),
> p.edit_start_time, 114), 1, 8) AS edit_start_time
> FROM dbo.na_rundown_tbl p
> WHERE (rundown_id NOT IN (SELECT ref1 FROM req_state_tbl WHERE (type =
> 401)))
> ) AS u
> INNER JOIN dbo.na_story_tbl AS s ON s.rundown_id = u.rundown_id
> LEFT OUTER JOIN dbo.na_item_tbl AS i ON s.story_id = i.story_id
> LEFT OUTER JOIN dbo.na_itemkeyword_tbl AS k ON i.item_id = k.item_id
> LEFT OUTER JOIN dbo.na_itemdesc_tbl AS d ON i.item_id = d.item_id where
> contains (u.title ,'%midlothian%')
> error message received:
> Msg 7601, Level 16, State 3, Line 1
> Cannot use a CONTAINS or FREETEXT predicate on column 'title' because it
> is
> not full-text indexed.
>
|||title is a field name . I type sp_help_fulltext_tables 'catalogname',
'tablename' it returns one row
I had created the index using the below script
create fulltext catalog cat1
create unique index ui_rundown_tbl on na_rundown_tbl (rundown_id)
create fulltext index on na_rundown_tbl (title)
key index ui_rundown_tbl on cat1 with change_tracking auto
Hilary Cotter wrote:[vbcol=seagreen]
>what does sp_help_fulltext_tables 'catalogname','title' return?
>make sure you replace catalogname with the name of your catalog.
>[quoted text clipped - 34 lines]
|||Hello ARPREET,
Move your contains inside the derived table.
u resolves to a derived table and not the underlying table na_rundown_tbl.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> SELECT top 400 s.story_id, u.title, s.title AS story_name, u.state,
> CONVERT(char(10), u.air_date, 101) AS rundown_date,
> '' AS video, '' AS cg_text, SUBSTRING(s.text, 1,500) AS script,
> SUBSTRING(i.
> text, 1, 500) AS item_text, i.type, i.content_status, k.keyword, i.
> editorial_description AS description, d.description AS notes,
> s.editor AS creator, i.original_material_id AS clipname,
> i.ar_material_id AS
> material_id
> FROM
> (
> SELECT NULL AS state, NULL AS type, p.rundown_id, p.ncs_rundown_id, p.
> edit_duration, p.title,
> CONVERT(char(10), p.air_date, 101) AS air_date,
> SUBSTRING(CONVERT(varchar(10),
> p.edit_start_time, 114), 1, 8) AS edit_start_time
> FROM dbo.na_rundown_tbl p
> WHERE (rundown_id NOT IN (SELECT ref1 FROM req_state_tbl WHERE (type =
> 401)))
> ) AS u
> INNER JOIN dbo.na_story_tbl AS s ON s.rundown_id = u.rundown_id
> LEFT OUTER JOIN dbo.na_item_tbl AS i ON s.story_id = i.story_id
> LEFT OUTER JOIN dbo.na_itemkeyword_tbl AS k ON i.item_id = k.item_id
> LEFT OUTER JOIN dbo.na_itemdesc_tbl AS d ON i.item_id = d.item_id
> where
> contains (u.title ,'%midlothian%')
the same field in an embedded sql it says cannot use contain on a field that
is not full text indexed.
example
SELECT top 400 s.story_id, u.title, s.title AS story_name, u.state,
CONVERT(char(10), u.air_date, 101) AS rundown_date,
'' AS video, '' AS cg_text, SUBSTRING(s.text, 1,500) AS script, SUBSTRING(i.
text, 1, 500) AS item_text, i.type, i.content_status, k.keyword, i.
editorial_description AS description, d.description AS notes,
s.editor AS creator, i.original_material_id AS clipname, i.ar_material_id AS
material_id
FROM
(
SELECT NULL AS state, NULL AS type, p.rundown_id, p.ncs_rundown_id, p.
edit_duration, p.title,
CONVERT(char(10), p.air_date, 101) AS air_date, SUBSTRING(CONVERT(varchar(10),
p.edit_start_time, 114), 1, 8) AS edit_start_time
FROM dbo.na_rundown_tbl p
WHERE (rundown_id NOT IN (SELECT ref1 FROM req_state_tbl WHERE (type = 401)))
) AS u
INNER JOIN dbo.na_story_tbl AS s ON s.rundown_id = u.rundown_id
LEFT OUTER JOIN dbo.na_item_tbl AS i ON s.story_id = i.story_id
LEFT OUTER JOIN dbo.na_itemkeyword_tbl AS k ON i.item_id = k.item_id
LEFT OUTER JOIN dbo.na_itemdesc_tbl AS d ON i.item_id = d.item_id where
contains (u.title ,'%midlothian%')
error message received:
Msg 7601, Level 16, State 3, Line 1
Cannot use a CONTAINS or FREETEXT predicate on column 'title' because it is
not full-text indexed.
what does sp_help_fulltext_tables 'catalogname','title' return?
make sure you replace catalogname with the name of your catalog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ARPREET" <u31535@.uwe> wrote in message news:6d68cc930bce5@.uwe...
>I added full text indexing on the title field in a table but when i
>reference
> the same field in an embedded sql it says cannot use contain on a field
> that
> is not full text indexed.
> example
> SELECT top 400 s.story_id, u.title, s.title AS story_name, u.state,
> CONVERT(char(10), u.air_date, 101) AS rundown_date,
> '' AS video, '' AS cg_text, SUBSTRING(s.text, 1,500) AS script,
> SUBSTRING(i.
> text, 1, 500) AS item_text, i.type, i.content_status, k.keyword, i.
> editorial_description AS description, d.description AS notes,
> s.editor AS creator, i.original_material_id AS clipname, i.ar_material_id
> AS
> material_id
> FROM
> (
> SELECT NULL AS state, NULL AS type, p.rundown_id, p.ncs_rundown_id, p.
> edit_duration, p.title,
> CONVERT(char(10), p.air_date, 101) AS air_date,
> SUBSTRING(CONVERT(varchar(10),
> p.edit_start_time, 114), 1, 8) AS edit_start_time
> FROM dbo.na_rundown_tbl p
> WHERE (rundown_id NOT IN (SELECT ref1 FROM req_state_tbl WHERE (type =
> 401)))
> ) AS u
> INNER JOIN dbo.na_story_tbl AS s ON s.rundown_id = u.rundown_id
> LEFT OUTER JOIN dbo.na_item_tbl AS i ON s.story_id = i.story_id
> LEFT OUTER JOIN dbo.na_itemkeyword_tbl AS k ON i.item_id = k.item_id
> LEFT OUTER JOIN dbo.na_itemdesc_tbl AS d ON i.item_id = d.item_id where
> contains (u.title ,'%midlothian%')
> error message received:
> Msg 7601, Level 16, State 3, Line 1
> Cannot use a CONTAINS or FREETEXT predicate on column 'title' because it
> is
> not full-text indexed.
>
|||title is a field name . I type sp_help_fulltext_tables 'catalogname',
'tablename' it returns one row
I had created the index using the below script
create fulltext catalog cat1
create unique index ui_rundown_tbl on na_rundown_tbl (rundown_id)
create fulltext index on na_rundown_tbl (title)
key index ui_rundown_tbl on cat1 with change_tracking auto
Hilary Cotter wrote:[vbcol=seagreen]
>what does sp_help_fulltext_tables 'catalogname','title' return?
>make sure you replace catalogname with the name of your catalog.
>[quoted text clipped - 34 lines]
|||Hello ARPREET,
Move your contains inside the derived table.
u resolves to a derived table and not the underlying table na_rundown_tbl.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> SELECT top 400 s.story_id, u.title, s.title AS story_name, u.state,
> CONVERT(char(10), u.air_date, 101) AS rundown_date,
> '' AS video, '' AS cg_text, SUBSTRING(s.text, 1,500) AS script,
> SUBSTRING(i.
> text, 1, 500) AS item_text, i.type, i.content_status, k.keyword, i.
> editorial_description AS description, d.description AS notes,
> s.editor AS creator, i.original_material_id AS clipname,
> i.ar_material_id AS
> material_id
> FROM
> (
> SELECT NULL AS state, NULL AS type, p.rundown_id, p.ncs_rundown_id, p.
> edit_duration, p.title,
> CONVERT(char(10), p.air_date, 101) AS air_date,
> SUBSTRING(CONVERT(varchar(10),
> p.edit_start_time, 114), 1, 8) AS edit_start_time
> FROM dbo.na_rundown_tbl p
> WHERE (rundown_id NOT IN (SELECT ref1 FROM req_state_tbl WHERE (type =
> 401)))
> ) AS u
> INNER JOIN dbo.na_story_tbl AS s ON s.rundown_id = u.rundown_id
> LEFT OUTER JOIN dbo.na_item_tbl AS i ON s.story_id = i.story_id
> LEFT OUTER JOIN dbo.na_itemkeyword_tbl AS k ON i.item_id = k.item_id
> LEFT OUTER JOIN dbo.na_itemdesc_tbl AS d ON i.item_id = d.item_id
> where
> contains (u.title ,'%midlothian%')
Monday, March 26, 2012
Fetch text from HTML [URGENT]
Hello everybody,
I have data in my sql server table that is entered with a rich text editor so it contains html formatting. I need to show this data on the form with the formatting user has specified while entering the data. On the crystal report, I need to show the data not in the html format but simple text. I created a stored procedure to fetch the data and binded the report with the stored procedure. When data is printed on the report, it shows HTML tags with the data. Is there a way to fetch text in SQL so that report will only show text without HTML formatting?
Thanks.
It is stored a just a string of characters. It is retreived as just a string of characters. (Some of those characters are html coding characters.
You may wish to create a User Defined Function (UDF) that will strip out the html characters and can be used when necessary.
Here is an example of how you might create and use such a function. (It is incomplete -I'm sure you will be able to add the additional html coding...)
CREATE FUNCTION dbo.StripHTML
( @.StringIn varchar(500) )
RETURNS varchar(500)
AS
BEGIN
SET @.StringIn = replace( replace( @.StringIn, '
', '' ), '
', '' )SET @.StringIn = replace( replace( @.StringIn, '<b>', '' ), '</b>', '' )
-- add addition html codes here
RETURN ( @.StringIn )
END
GO
SELECT dbo.StripHTML('
This is a <b>Test</b>
')
|||
I was looking for a quicker and easier way to do it (if there is something). As you said, I am writing my own UDF to parse HTML.
Thanks a lot.
|||You can use the following code to replace all the HTML tags....
CREATE Function dbo.Html2Text(@.HtmlString As Varchar(8000))
Returns Varchar(8000)
as
Begin
Declare @.TagString As Varchar(8000)
Declare @.TagStart As int
Declare @.TagEnd As int
Select @.TagStart = CharIndex('<', @.HtmlString),
@.TagEnd = CharIndex('>', @.HtmlString)
While @.TagStart <> 0 And @.TagEnd <> 0 And @.TagEnd > @.TagStart
Begin
Select @.TagString = Substring(@.HtmlString, @.TagStart, @.TagEnd - @.TagStart + 1)
Select @.HtmlString = Replace(@.HtmlString, @.TagString, '')
Select @.TagStart = CharIndex('<', @.HtmlString),
@.TagEnd = CharIndex('>', @.HtmlString)
End
Select @.HtmlString = Replace(@.HtmlString, ' ', ' ')
Select @.HtmlString = Replace(@.HtmlString, '&', '&')
Select @.HtmlString = Replace(@.HtmlString, '"', '''')
Select @.HtmlString = Replace(@.HtmlString, '&#', '#')
Select @.HtmlString = Replace(@.HtmlString, '<', '<')
Select @.HtmlString = Replace(@.HtmlString, '>', '>')
Select @.HtmlString = Replace(@.HtmlString, '%20', ' ')
Select @.HtmlString = Replace(@.HtmlString, Char(10), '')
Select @.HtmlString = Replace(@.HtmlString, Char(13), '')
Select @.HtmlString = LTrim(RTrim(@.HtmlString))
Return @.HtmlString
End
Go
Select dbo.Html2Text('
Test Data
'); -- Result :Test DataFriday, March 23, 2012
Features of a normal index comapred to a Full text index
Hello everyone,
My company is in the middle of coding a complex search function which
takes over most of the features that full text indexing contains (such
as stemming and stopping).
However from reading about full text indexing it appears that for a
column containing rows of pure text documents, the way that it indexes
the text is as follows: -
word id of row where word can be found
### #################################
car 0, 10, 256, 654
bike 20, 36, 92
skates 65,. 42
If this is the case I can see this improving search speeds for a large
database.
However we don't want any of the other features that full text
catalogue has as we do it ourselves.
My question is can we turn off all the extra features of full text
indexing (so we only left with a text index), or is a normal index
sufficient to provide the above example?
Many thanks for any help or advice
Kind Regards
Philip
With the Contains/ContainsTable keywords you get a strict match which it
sounds like what you are looking for. In otherwords stemming, and
wildcarding are disabled.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Phi!" <philgoogle2003@.yahoo.com> wrote in message
news:42d3fa28.0408110112.15962c0@.posting.google.co m...
> Hello everyone,
> My company is in the middle of coding a complex search function which
> takes over most of the features that full text indexing contains (such
> as stemming and stopping).
> However from reading about full text indexing it appears that for a
> column containing rows of pure text documents, the way that it indexes
> the text is as follows: -
> word id of row where word can be found
> ### #################################
> car 0, 10, 256, 654
> bike 20, 36, 92
> skates 65,. 42
> If this is the case I can see this improving search speeds for a large
> database.
> However we don't want any of the other features that full text
> catalogue has as we do it ourselves.
> My question is can we turn off all the extra features of full text
> indexing (so we only left with a text index), or is a normal index
> sufficient to provide the above example?
> Many thanks for any help or advice
> Kind Regards
> Philip
|||Hello Hilary,
Thankyou for you help, just to make sure, when I enable full text
catalogs, does it index the text without stemming and stopping the
data.
By this i mean that is I had stored in the table "to be or not to be",
would the full text catalog contain indexes for to, be, or and not or
would it stem and stop the text.
Again thankyou for your help
Kind Regards
Phil
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> With the Contains/ContainsTable keywords you get a strict match which it
> sounds like what you are looking for. In otherwords stemming, and
> wildcarding are disabled.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Phi!" <philgoogle2003@.yahoo.com> wrote in message
> news:42d3fa28.0408110112.15962c0@.posting.google.co m...
|||The answer is complex. First off to,be, or, and not are all noise words and
as such they would not be indexed.
But to answer your question that depends on the word breaker. In general
the words would be indexed as they appear in the content. For some word
breakers different language rules dicate different indexing patterns, for
instance in the French word breaker, marie-claire is indexed as two
different words, marie and claire, however Marie-Claire is indexed as one
word (MarieClaire). The hyphen and the capitalization cause it to be indexed
as one word not two.
It is at query time when the search arguements might will be stemmed (when
you are doing a FreeText query, or an Inflectional query). If you are doing
a Contains query without wildcarding or the FormsOf(Inflectional type
queries you won't get stemming (although there are word breaker specific
some exceptions).
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Phi!" <philgoogle2003@.yahoo.com> wrote in message
news:42d3fa28.0408120050.2b0d4d81@.posting.google.c om...
> Hello Hilary,
> Thankyou for you help, just to make sure, when I enable full text
> catalogs, does it index the text without stemming and stopping the
> data.
> By this i mean that is I had stored in the table "to be or not to be",
> would the full text catalog contain indexes for to, be, or and not or
> would it stem and stop the text.
> Again thankyou for your help
> Kind Regards
> Phil
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
|||Phi!
In addition to what Hilary says, there is another factor here and that is
the OS platform (Win2K vs. Win2003 or WinXP) that you have SQL Server
installed on. Specifically, the Windows 2000 Server (Win2K) wordbreaker -
infosoft.dll - indexes the "-" (dash or hyphen) in "7-UP" as one token,
i.e., as single phrase. A work around for this is to drop and re-create your
FT Catalog and use the Neutral "Language for Word Breaker" for the your
FT-enabled column. However, with the Neutral "Language for Word Breaker",
you will lose the formsof(inflectional) function as the words are "broken"
into tokens based upon the "white space" between words...
However, this is not the case with Windows Server 2003 (Win2003) or Windows
XP (WinXP) as these OS-platforms, ships with a newer (or better, i.e., more
expectant results) wordbreaker - langwrbk.dll which would correctly (or more
expectant results) break 7 and UP into separate tokens. So, in the long run
to get both the correct wordbreaking for you as well as the use of the
formsof(inflectional) function, if this is the functionality you're looking
for, you should consider upgrading to Win2003...
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u4tYhaGgEHA.1188@.TK2MSFTNGP11.phx.gbl...
> The answer is complex. First off to,be, or, and not are all noise words
and
> as such they would not be indexed.
> But to answer your question that depends on the word breaker. In general
> the words would be indexed as they appear in the content. For some word
> breakers different language rules dicate different indexing patterns, for
> instance in the French word breaker, marie-claire is indexed as two
> different words, marie and claire, however Marie-Claire is indexed as one
> word (MarieClaire). The hyphen and the capitalization cause it to be
indexed
> as one word not two.
> It is at query time when the search arguements might will be stemmed (when
> you are doing a FreeText query, or an Inflectional query). If you are
doing[vbcol=seagreen]
> a Contains query without wildcarding or the FormsOf(Inflectional type
> queries you won't get stemming (although there are word breaker specific
> some exceptions).
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Phi!" <philgoogle2003@.yahoo.com> wrote in message
> news:42d3fa28.0408120050.2b0d4d81@.posting.google.c om...
> news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...
it[vbcol=seagreen]
which[vbcol=seagreen]
(such[vbcol=seagreen]
indexes[vbcol=seagreen]
large
>
|||Hello Hillary,
AFter much testing we have decided to go as follows, we will use Full
Text catalogs and search using the contains keyword.
However to ensure SQL 2000 server does not do anything strange with
the results we have turned the lnaguage setting to neutral. We found
the results were not returned when we had the UK setting on. i.e.
"jpg" would not find "filename.jpg".
For wildcards we will use the LIKEkeyword as the contains does not do
patterm matching with wilcards but tries to find differenet words
instead.
Therefore we hope we have the best of both world, by the using the
contains in most cases but the the LIKE keyword whenever anyone needs
pattern matching (which should be rare).
Again thankyou for your hillary in helping me understanding full text
catlogs
Have a good weekend
Phil
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<u4tYhaGgEHA.1188@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> The answer is complex. First off to,be, or, and not are all noise words and
> as such they would not be indexed.
> But to answer your question that depends on the word breaker. In general
> the words would be indexed as they appear in the content. For some word
> breakers different language rules dicate different indexing patterns, for
> instance in the French word breaker, marie-claire is indexed as two
> different words, marie and claire, however Marie-Claire is indexed as one
> word (MarieClaire). The hyphen and the capitalization cause it to be indexed
> as one word not two.
> It is at query time when the search arguements might will be stemmed (when
> you are doing a FreeText query, or an Inflectional query). If you are doing
> a Contains query without wildcarding or the FormsOf(Inflectional type
> queries you won't get stemming (although there are word breaker specific
> some exceptions).
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Phi!" <philgoogle2003@.yahoo.com> wrote in message
> news:42d3fa28.0408120050.2b0d4d81@.posting.google.c om...
> news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...
|||Phi!,
Just FYI, if you were able to read my posting to this thread, you would of
understood why "the results were not returned when we had the UK setting on.
i.e. "jpg" would not find 'filename.jpg'" as it is a bug in the Win2K
wordbreaker dll and if in the future you decide to upgrade to Windows Server
2003 (Win2003) you would not of encountered this bug.
Regards,
John
"Phi!" <philgoogle2003@.yahoo.com> wrote in message
news:42d3fa28.0408130807.3fa93afe@.posting.google.c om...
> Hello Hillary,
> AFter much testing we have decided to go as follows, we will use Full
> Text catalogs and search using the contains keyword.
> However to ensure SQL 2000 server does not do anything strange with
> the results we have turned the lnaguage setting to neutral. We found
> the results were not returned when we had the UK setting on. i.e.
> "jpg" would not find "filename.jpg".
> For wildcards we will use the LIKEkeyword as the contains does not do
> patterm matching with wilcards but tries to find differenet words
> instead.
> Therefore we hope we have the best of both world, by the using the
> contains in most cases but the the LIKE keyword whenever anyone needs
> pattern matching (which should be rare).
> Again thankyou for your hillary in helping me understanding full text
> catlogs
> Have a good weekend
> Phil
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
news:<u4tYhaGgEHA.1188@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
and[vbcol=seagreen]
general[vbcol=seagreen]
for[vbcol=seagreen]
one[vbcol=seagreen]
indexed[vbcol=seagreen]
(when[vbcol=seagreen]
doing[vbcol=seagreen]
which it[vbcol=seagreen]
which[vbcol=seagreen]
(such[vbcol=seagreen]
a[vbcol=seagreen]
indexes[vbcol=seagreen]
large[vbcol=seagreen]
|||Hi John!
Thankyou for the tip, will bear it in mind when we upgrade.
I am having trouble getting an newsgroup reader on my desktop at work
(compnay ploicy etc) so have been limited to google at the moment so
did not know you had posted until today!
sorry for any confusion
Phil
"John Kane" <jt-kane@.comcast.net> wrote in message news:<O4zBxiagEHA.3632@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> Phi!,
> Just FYI, if you were able to read my posting to this thread, you would of
> understood why "the results were not returned when we had the UK setting on.
> i.e. "jpg" would not find 'filename.jpg'" as it is a bug in the Win2K
> wordbreaker dll and if in the future you decide to upgrade to Windows Server
> 2003 (Win2003) you would not of encountered this bug.
> Regards,
> John
>
>
> "Phi!" <philgoogle2003@.yahoo.com> wrote in message
> news:42d3fa28.0408130807.3fa93afe@.posting.google.c om...
> news:<u4tYhaGgEHA.1188@.TK2MSFTNGP11.phx.gbl>...
> and
> general
> for
> one
> indexed
> (when
> doing
> news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...
> which it
> which
> (such
> a
> indexes
> large
My company is in the middle of coding a complex search function which
takes over most of the features that full text indexing contains (such
as stemming and stopping).
However from reading about full text indexing it appears that for a
column containing rows of pure text documents, the way that it indexes
the text is as follows: -
word id of row where word can be found
### #################################
car 0, 10, 256, 654
bike 20, 36, 92
skates 65,. 42
If this is the case I can see this improving search speeds for a large
database.
However we don't want any of the other features that full text
catalogue has as we do it ourselves.
My question is can we turn off all the extra features of full text
indexing (so we only left with a text index), or is a normal index
sufficient to provide the above example?
Many thanks for any help or advice
Kind Regards
Philip
With the Contains/ContainsTable keywords you get a strict match which it
sounds like what you are looking for. In otherwords stemming, and
wildcarding are disabled.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Phi!" <philgoogle2003@.yahoo.com> wrote in message
news:42d3fa28.0408110112.15962c0@.posting.google.co m...
> Hello everyone,
> My company is in the middle of coding a complex search function which
> takes over most of the features that full text indexing contains (such
> as stemming and stopping).
> However from reading about full text indexing it appears that for a
> column containing rows of pure text documents, the way that it indexes
> the text is as follows: -
> word id of row where word can be found
> ### #################################
> car 0, 10, 256, 654
> bike 20, 36, 92
> skates 65,. 42
> If this is the case I can see this improving search speeds for a large
> database.
> However we don't want any of the other features that full text
> catalogue has as we do it ourselves.
> My question is can we turn off all the extra features of full text
> indexing (so we only left with a text index), or is a normal index
> sufficient to provide the above example?
> Many thanks for any help or advice
> Kind Regards
> Philip
|||Hello Hilary,
Thankyou for you help, just to make sure, when I enable full text
catalogs, does it index the text without stemming and stopping the
data.
By this i mean that is I had stored in the table "to be or not to be",
would the full text catalog contain indexes for to, be, or and not or
would it stem and stop the text.
Again thankyou for your help
Kind Regards
Phil
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> With the Contains/ContainsTable keywords you get a strict match which it
> sounds like what you are looking for. In otherwords stemming, and
> wildcarding are disabled.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Phi!" <philgoogle2003@.yahoo.com> wrote in message
> news:42d3fa28.0408110112.15962c0@.posting.google.co m...
|||The answer is complex. First off to,be, or, and not are all noise words and
as such they would not be indexed.
But to answer your question that depends on the word breaker. In general
the words would be indexed as they appear in the content. For some word
breakers different language rules dicate different indexing patterns, for
instance in the French word breaker, marie-claire is indexed as two
different words, marie and claire, however Marie-Claire is indexed as one
word (MarieClaire). The hyphen and the capitalization cause it to be indexed
as one word not two.
It is at query time when the search arguements might will be stemmed (when
you are doing a FreeText query, or an Inflectional query). If you are doing
a Contains query without wildcarding or the FormsOf(Inflectional type
queries you won't get stemming (although there are word breaker specific
some exceptions).
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Phi!" <philgoogle2003@.yahoo.com> wrote in message
news:42d3fa28.0408120050.2b0d4d81@.posting.google.c om...
> Hello Hilary,
> Thankyou for you help, just to make sure, when I enable full text
> catalogs, does it index the text without stemming and stopping the
> data.
> By this i mean that is I had stored in the table "to be or not to be",
> would the full text catalog contain indexes for to, be, or and not or
> would it stem and stop the text.
> Again thankyou for your help
> Kind Regards
> Phil
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
|||Phi!
In addition to what Hilary says, there is another factor here and that is
the OS platform (Win2K vs. Win2003 or WinXP) that you have SQL Server
installed on. Specifically, the Windows 2000 Server (Win2K) wordbreaker -
infosoft.dll - indexes the "-" (dash or hyphen) in "7-UP" as one token,
i.e., as single phrase. A work around for this is to drop and re-create your
FT Catalog and use the Neutral "Language for Word Breaker" for the your
FT-enabled column. However, with the Neutral "Language for Word Breaker",
you will lose the formsof(inflectional) function as the words are "broken"
into tokens based upon the "white space" between words...
However, this is not the case with Windows Server 2003 (Win2003) or Windows
XP (WinXP) as these OS-platforms, ships with a newer (or better, i.e., more
expectant results) wordbreaker - langwrbk.dll which would correctly (or more
expectant results) break 7 and UP into separate tokens. So, in the long run
to get both the correct wordbreaking for you as well as the use of the
formsof(inflectional) function, if this is the functionality you're looking
for, you should consider upgrading to Win2003...
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u4tYhaGgEHA.1188@.TK2MSFTNGP11.phx.gbl...
> The answer is complex. First off to,be, or, and not are all noise words
and
> as such they would not be indexed.
> But to answer your question that depends on the word breaker. In general
> the words would be indexed as they appear in the content. For some word
> breakers different language rules dicate different indexing patterns, for
> instance in the French word breaker, marie-claire is indexed as two
> different words, marie and claire, however Marie-Claire is indexed as one
> word (MarieClaire). The hyphen and the capitalization cause it to be
indexed
> as one word not two.
> It is at query time when the search arguements might will be stemmed (when
> you are doing a FreeText query, or an Inflectional query). If you are
doing[vbcol=seagreen]
> a Contains query without wildcarding or the FormsOf(Inflectional type
> queries you won't get stemming (although there are word breaker specific
> some exceptions).
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Phi!" <philgoogle2003@.yahoo.com> wrote in message
> news:42d3fa28.0408120050.2b0d4d81@.posting.google.c om...
> news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...
it[vbcol=seagreen]
which[vbcol=seagreen]
(such[vbcol=seagreen]
indexes[vbcol=seagreen]
large
>
|||Hello Hillary,
AFter much testing we have decided to go as follows, we will use Full
Text catalogs and search using the contains keyword.
However to ensure SQL 2000 server does not do anything strange with
the results we have turned the lnaguage setting to neutral. We found
the results were not returned when we had the UK setting on. i.e.
"jpg" would not find "filename.jpg".
For wildcards we will use the LIKEkeyword as the contains does not do
patterm matching with wilcards but tries to find differenet words
instead.
Therefore we hope we have the best of both world, by the using the
contains in most cases but the the LIKE keyword whenever anyone needs
pattern matching (which should be rare).
Again thankyou for your hillary in helping me understanding full text
catlogs
Have a good weekend
Phil
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<u4tYhaGgEHA.1188@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> The answer is complex. First off to,be, or, and not are all noise words and
> as such they would not be indexed.
> But to answer your question that depends on the word breaker. In general
> the words would be indexed as they appear in the content. For some word
> breakers different language rules dicate different indexing patterns, for
> instance in the French word breaker, marie-claire is indexed as two
> different words, marie and claire, however Marie-Claire is indexed as one
> word (MarieClaire). The hyphen and the capitalization cause it to be indexed
> as one word not two.
> It is at query time when the search arguements might will be stemmed (when
> you are doing a FreeText query, or an Inflectional query). If you are doing
> a Contains query without wildcarding or the FormsOf(Inflectional type
> queries you won't get stemming (although there are word breaker specific
> some exceptions).
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Phi!" <philgoogle2003@.yahoo.com> wrote in message
> news:42d3fa28.0408120050.2b0d4d81@.posting.google.c om...
> news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...
|||Phi!,
Just FYI, if you were able to read my posting to this thread, you would of
understood why "the results were not returned when we had the UK setting on.
i.e. "jpg" would not find 'filename.jpg'" as it is a bug in the Win2K
wordbreaker dll and if in the future you decide to upgrade to Windows Server
2003 (Win2003) you would not of encountered this bug.
Regards,
John
"Phi!" <philgoogle2003@.yahoo.com> wrote in message
news:42d3fa28.0408130807.3fa93afe@.posting.google.c om...
> Hello Hillary,
> AFter much testing we have decided to go as follows, we will use Full
> Text catalogs and search using the contains keyword.
> However to ensure SQL 2000 server does not do anything strange with
> the results we have turned the lnaguage setting to neutral. We found
> the results were not returned when we had the UK setting on. i.e.
> "jpg" would not find "filename.jpg".
> For wildcards we will use the LIKEkeyword as the contains does not do
> patterm matching with wilcards but tries to find differenet words
> instead.
> Therefore we hope we have the best of both world, by the using the
> contains in most cases but the the LIKE keyword whenever anyone needs
> pattern matching (which should be rare).
> Again thankyou for your hillary in helping me understanding full text
> catlogs
> Have a good weekend
> Phil
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
news:<u4tYhaGgEHA.1188@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
and[vbcol=seagreen]
general[vbcol=seagreen]
for[vbcol=seagreen]
one[vbcol=seagreen]
indexed[vbcol=seagreen]
(when[vbcol=seagreen]
doing[vbcol=seagreen]
which it[vbcol=seagreen]
which[vbcol=seagreen]
(such[vbcol=seagreen]
a[vbcol=seagreen]
indexes[vbcol=seagreen]
large[vbcol=seagreen]
|||Hi John!
Thankyou for the tip, will bear it in mind when we upgrade.
I am having trouble getting an newsgroup reader on my desktop at work
(compnay ploicy etc) so have been limited to google at the moment so
did not know you had posted until today!
sorry for any confusion
Phil
"John Kane" <jt-kane@.comcast.net> wrote in message news:<O4zBxiagEHA.3632@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> Phi!,
> Just FYI, if you were able to read my posting to this thread, you would of
> understood why "the results were not returned when we had the UK setting on.
> i.e. "jpg" would not find 'filename.jpg'" as it is a bug in the Win2K
> wordbreaker dll and if in the future you decide to upgrade to Windows Server
> 2003 (Win2003) you would not of encountered this bug.
> Regards,
> John
>
>
> "Phi!" <philgoogle2003@.yahoo.com> wrote in message
> news:42d3fa28.0408130807.3fa93afe@.posting.google.c om...
> news:<u4tYhaGgEHA.1188@.TK2MSFTNGP11.phx.gbl>...
> and
> general
> for
> one
> indexed
> (when
> doing
> news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...
> which it
> which
> (such
> a
> indexes
> large
Monday, March 19, 2012
Fatal Error 7105 ... text, ntext, or image node does not exist
I'm getting the following in error in Enterprise Manager:
Fatal Error 7105 ... text, ntext, or image node does not exist
It seems that a row in a table has become corrupt or something because when
I try to query for that row or scroll down in the table to view the row (the
preceding rows display fine), that is when I get this error. Can a row that
causes this error be salvaged or deleted or something? It seems it is this
one row that is causing the problem. Is that right?
I found this KB article that suggests applying a hotfix.
http://support.microsoft.com/kb/890755
The hotfix seems to have come out between SP 3a & SP 4. I already have
Service Pack 4 installed, so I'm wondering if this proposed hotfix is already
part of SP 4 or if I should try to apply the hotfix?
I don't want to mess up my box, but I need this error resolved.
If anyone can offer furter advice, I would appreciated (and then I can get a
broken app back up and running).
Thanks
I have a correction to my post.
I'm getting the following error in Enterprise Manager:
[Microsoft][ODBC SQL Server Driver][SQL Server]Page (1:6890), slot 35 for
text, ntext, or image node does not exist.
In my web app, the error is:
Fatal Error 7105 ... text, ntext, or image node does not exist
Fatal Error 7105 ... text, ntext, or image node does not exist
It seems that a row in a table has become corrupt or something because when
I try to query for that row or scroll down in the table to view the row (the
preceding rows display fine), that is when I get this error. Can a row that
causes this error be salvaged or deleted or something? It seems it is this
one row that is causing the problem. Is that right?
I found this KB article that suggests applying a hotfix.
http://support.microsoft.com/kb/890755
The hotfix seems to have come out between SP 3a & SP 4. I already have
Service Pack 4 installed, so I'm wondering if this proposed hotfix is already
part of SP 4 or if I should try to apply the hotfix?
I don't want to mess up my box, but I need this error resolved.
If anyone can offer furter advice, I would appreciated (and then I can get a
broken app back up and running).
Thanks
I have a correction to my post.
I'm getting the following error in Enterprise Manager:
[Microsoft][ODBC SQL Server Driver][SQL Server]Page (1:6890), slot 35 for
text, ntext, or image node does not exist.
In my web app, the error is:
Fatal Error 7105 ... text, ntext, or image node does not exist
Fatal Error 7105 ... text, ntext, or image node does not exist
I'm getting the following in error in Enterprise Manager:
Fatal Error 7105 ... text, ntext, or image node does not exist
It seems that a row in a table has become corrupt or something because when
I try to query for that row or scroll down in the table to view the row (the
preceding rows display fine), that is when I get this error. Can a row that
causes this error be salvaged or deleted or something? It seems it is this
one row that is causing the problem. Is that right?
I found this KB article that suggests applying a hotfix.
http://support.microsoft.com/kb/890755
The hotfix seems to have come out between SP 3a & SP 4. I already have
Service Pack 4 installed, so I'm wondering if this proposed hotfix is alread
y
part of SP 4 or if I should try to apply the hotfix?
I don't want to mess up my box, but I need this error resolved.
If anyone can offer furter advice, I would appreciated (and then I can get a
broken app back up and running).
ThanksI have a correction to my post.
I'm getting the following error in Enterprise Manager:
[Microsoft][ODBC SQL Server Driver][SQL Server]Page (1:6890), sl
ot 35 for
text, ntext, or image node does not exist.
In my web app, the error is:
Fatal Error 7105 ... text, ntext, or image node does not exist
Fatal Error 7105 ... text, ntext, or image node does not exist
It seems that a row in a table has become corrupt or something because when
I try to query for that row or scroll down in the table to view the row (the
preceding rows display fine), that is when I get this error. Can a row that
causes this error be salvaged or deleted or something? It seems it is this
one row that is causing the problem. Is that right?
I found this KB article that suggests applying a hotfix.
http://support.microsoft.com/kb/890755
The hotfix seems to have come out between SP 3a & SP 4. I already have
Service Pack 4 installed, so I'm wondering if this proposed hotfix is alread
y
part of SP 4 or if I should try to apply the hotfix?
I don't want to mess up my box, but I need this error resolved.
If anyone can offer furter advice, I would appreciated (and then I can get a
broken app back up and running).
ThanksI have a correction to my post.
I'm getting the following error in Enterprise Manager:
[Microsoft][ODBC SQL Server Driver][SQL Server]Page (1:6890), sl
ot 35 for
text, ntext, or image node does not exist.
In my web app, the error is:
Fatal Error 7105 ... text, ntext, or image node does not exist
Fatal Error 7105 ... text, ntext, or image node does not exist
I'm getting the following in error in Enterprise Manager:
Fatal Error 7105 ... text, ntext, or image node does not exist
It seems that a row in a table has become corrupt or something because when
I try to query for that row or scroll down in the table to view the row (the
preceding rows display fine), that is when I get this error. Can a row that
causes this error be salvaged or deleted or something? It seems it is this
one row that is causing the problem. Is that right?
I found this KB article that suggests applying a hotfix.
http://support.microsoft.com/kb/890755
The hotfix seems to have come out between SP 3a & SP 4. I already have
Service Pack 4 installed, so I'm wondering if this proposed hotfix is already
part of SP 4 or if I should try to apply the hotfix?
I don't want to mess up my box, but I need this error resolved.
If anyone can offer furter advice, I would appreciated (and then I can get a
broken app back up and running).
ThanksI have a correction to my post.
I'm getting the following error in Enterprise Manager:
[Microsoft][ODBC SQL Server Driver][SQL Server]Page (1:6890), slot 35 for
text, ntext, or image node does not exist.
In my web app, the error is:
Fatal Error 7105 ... text, ntext, or image node does not exist
Fatal Error 7105 ... text, ntext, or image node does not exist
It seems that a row in a table has become corrupt or something because when
I try to query for that row or scroll down in the table to view the row (the
preceding rows display fine), that is when I get this error. Can a row that
causes this error be salvaged or deleted or something? It seems it is this
one row that is causing the problem. Is that right?
I found this KB article that suggests applying a hotfix.
http://support.microsoft.com/kb/890755
The hotfix seems to have come out between SP 3a & SP 4. I already have
Service Pack 4 installed, so I'm wondering if this proposed hotfix is already
part of SP 4 or if I should try to apply the hotfix?
I don't want to mess up my box, but I need this error resolved.
If anyone can offer furter advice, I would appreciated (and then I can get a
broken app back up and running).
ThanksI have a correction to my post.
I'm getting the following error in Enterprise Manager:
[Microsoft][ODBC SQL Server Driver][SQL Server]Page (1:6890), slot 35 for
text, ntext, or image node does not exist.
In my web app, the error is:
Fatal Error 7105 ... text, ntext, or image node does not exist
Friday, March 9, 2012
Fastest method for Inserting 1 million records into SQL Database
I am reading a text file and modifing the data to match fields in a SQL 2000 Database then inserting the record in. I am using vb.net and have tried various methods but all are to slow. I would appreciate any help anybody could offer.
Have you tried BCP or BULK INSERT? It sounds like you're inserting the data
row-by-row using VB.NET; perhaps you can get away with a BULK INSERT using a
format file to tweak the data? It would probably also be faster if you BULK
INSERT the data in its "raw" form into a temporary table and then move it to
its final destination using INSERT ... SELECT, and do any data modifications
necessary in the SELECT.
"BradC" <BradC@.discussions.microsoft.com> wrote in message
news:F2AA271E-A8B7-448F-83BC-900EFFB679E8@.microsoft.com...
> I am reading a text file and modifing the data to match fields in a SQL
2000 Database then inserting the record in. I am using vb.net and have tried
various methods but all are to slow. I would appreciate any help anybody
could offer.
Have you tried BCP or BULK INSERT? It sounds like you're inserting the data
row-by-row using VB.NET; perhaps you can get away with a BULK INSERT using a
format file to tweak the data? It would probably also be faster if you BULK
INSERT the data in its "raw" form into a temporary table and then move it to
its final destination using INSERT ... SELECT, and do any data modifications
necessary in the SELECT.
"BradC" <BradC@.discussions.microsoft.com> wrote in message
news:F2AA271E-A8B7-448F-83BC-900EFFB679E8@.microsoft.com...
> I am reading a text file and modifing the data to match fields in a SQL
2000 Database then inserting the record in. I am using vb.net and have tried
various methods but all are to slow. I would appreciate any help anybody
could offer.
Fastest method for Inserting 1 million records into SQL Database
I am reading a text file and modifing the data to match fields in a SQL 2000
Database then inserting the record in. I am using vb.net and have tried var
ious methods but all are to slow. I would appreciate any help anybody could
offer.Have you tried BCP or BULK INSERT? It sounds like you're inserting the data
row-by-row using VB.NET; perhaps you can get away with a BULK INSERT using a
format file to tweak the data? It would probably also be faster if you BULK
INSERT the data in its "raw" form into a temporary table and then move it to
its final destination using INSERT ... SELECT, and do any data modifications
necessary in the SELECT.
"BradC" <BradC@.discussions.microsoft.com> wrote in message
news:F2AA271E-A8B7-448F-83BC-900EFFB679E8@.microsoft.com...
> I am reading a text file and modifing the data to match fields in a SQL
2000 Database then inserting the record in. I am using vb.net and have tried
various methods but all are to slow. I would appreciate any help anybody
could offer.
Database then inserting the record in. I am using vb.net and have tried var
ious methods but all are to slow. I would appreciate any help anybody could
offer.Have you tried BCP or BULK INSERT? It sounds like you're inserting the data
row-by-row using VB.NET; perhaps you can get away with a BULK INSERT using a
format file to tweak the data? It would probably also be faster if you BULK
INSERT the data in its "raw" form into a temporary table and then move it to
its final destination using INSERT ... SELECT, and do any data modifications
necessary in the SELECT.
"BradC" <BradC@.discussions.microsoft.com> wrote in message
news:F2AA271E-A8B7-448F-83BC-900EFFB679E8@.microsoft.com...
> I am reading a text file and modifing the data to match fields in a SQL
2000 Database then inserting the record in. I am using vb.net and have tried
various methods but all are to slow. I would appreciate any help anybody
could offer.
Sunday, February 26, 2012
False "LIKE" hits
Hi All... We have an application where a table has a field of type text that contains readable text, but some of it may have an HTML tag in it - specifically an HTML <img> tag. For example, it may be something like:
BLAH BLAH BLAH <img src='http://pics.10026.com/?src=RetrieveImage.aspx?ImageId=1234'> BLAH BLAH BLAH.
The problem we're seeing is that when searching that field using LIKE, it's returning records that do in fact satisfy the SQL query, but we'd like it not to. That is, we'd like to exclude those HTML tags from the search.
For example:
SELECT ... WHERE TextField LIKE '%retr%'
is returning records that have those HTML tags. Yes, I know SQL is only doing its job... Anyone have any ideas to exclude those tags from the LIKE search? Thanks! -- Curt
Curt, I'm not clear on if you want to filter out the rows that have ANY html tags? or you're saying that part of a single column has a mixture of data AND html and you'd want to search only the non-html part of the column?
So, if you search this string below for the word "White" you want the row, but if you search on the word "Image" you do NOT want the row?
White Horse <img src='http://pics.10026.com/?src=RetrieveImage.aspx?ImageId=1234'>
so, you want to filter out everything between the "<" and ">" ?
You could write a function that parses each line, and searches for your string...
Bruce
|||If I understand you correctly, you want the return to be:
BLAH BLAH BLAH ... BLAH BLAH BLAH
If that is a correct interpretation, it's not going to be either easy or pretty. By that I mean you will have to parse the data fields on the search which is really going to increase the time required to search. Indexes will not be used.
If this is the path you wish to take, you will need to create a User Defined Function that will take the entire field and strip out all characters between paired angle brackets.
|||
Thanks for the replies, Bruce and Arnie. I'm sorry for not making the issue very clear - believe it or not, it took me some time to figure out the wording I did manage to get down...
Using the statement SELECT * From TheTable WHERE TheField LIKE '%RETR%'
I would want the following record to be included:
BLAH RETR BLAH <img src='http://pics.10026.com/?src=RetrieveImage.aspx?ImageId=1234'> BLAH BLAH BLAH
But I would NOT want the following record included:
BLAH BLAH BLAH <img src='http://pics.10026.com/?src=RetrieveImage.aspx?ImageId=1234'> BLAH BLAH BLAH
In any records that are included, I would want the text returned as it appears - that is nothing filtered out.
You both pointed in the direction of writing a function to filter out that "<....>" data before applying a search. And yeah, that just adds to the overhead of the search... And quite frankly, I'm a bit nervous about that anyway - there's gonna be alot of these records and that LIKE just seems expensive. I've also been looking at indexing the text and using CONTAINS (that sound right?). We're also considering a sort of application-specific index of the text before we put it in the table as alot of the searchs are somewhat predictable.
|||If there is only a single tag per row, you could have a WHERE clause that includes BOTH the substring that precedes "<" and the substring that follows ">". That should not require a function.
If there are multiple "<...>" entries per row, this method would not work as desired.
Dan
|||IF, and that is a big IF in my opinion, the data is consistant and your sample correctly reflects the search value, this could work:
Code Snippet
DECLARE @.MyTable table
( RowID int IDENTITY,
Comment varchar(max)
)
INSERT INTO @.MyTable VALUES ( 'BLAH RETR BLAH <img src='http://pics.10026.com/?src='RetrieveImage.aspx?ImageId=1234''> BLAH BLAH BLAH' )
INSERT INTO @.MyTable VALUES ( 'BLAH BLAH BLAH <img src='http://pics.10026.com/?src='RetrieveImage.aspx?ImageId=1234''> BLAH BLAH BLAH' )
SELECT Comment
FROM @.MyTable
WHERE ( Comment LIKE '% RETR %'
AND Comment NOT LIKE '%=''RETR'
)
And of course, you could 'build up' the search values using parameters and constants.
This feels so 'unclean' that now I have to go take a shower...
slightly shorter version.
Code Snippet
SELECT CommentFROM @.MyTable
WHERE (Comment LIKE '%[ ]RETR[ ]%')|||This problem was born for regular expressions:
http://msdn.microsoft.com/msdnmag/issues/07/02/SQLRegex/default.aspx
you could also use charindex:
Code Snippet
SELECT *
From TheTable
where charindex('RETR', TheField)
not between charindex('<', TheField)
and charindex('>', TheField)
|||Spent far too long on this but here goes.The following allows for any number of HTML Tags in the field.
Tested with the following:
Code Snippet
SELECT * into TheTable
From(
select 'BLAH RETR BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH' as TheField
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH'
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH RETR BLAH <img src="RetrieveImage.aspx?ImageId=1234"> '
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> '
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAHRETRBLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> '
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> RETR BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> '
) as t1
create a table Numbers with a single field Num populated 1 to x where x = a number larger than the longest field you expect to work with:
Code Snippet
select * into numbers from (
select 1 as Num union all
select 2 as Num union all
select 3 as Num union all
...
select 2999 as Num union all
select 3000 as Num) as T1
Then use this query:
Code Snippet
select distinct TheField
from (
select
TheField,
case
when substring(TheField, num,1) = '>'
or num = 1
then substring(TheField, Num+1, charindex('<',TheField+'<', Num) - Num -1 )
else ''
end as c3
from TheTable, Numbers
) as T1
where c3 like '%RETR%'
Subscribe to:
Posts (Atom)