Search a full text index using special characters

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

SQL Server 2008 Forums

Transact-SQL (2008)

Search a full text index using special characters

Author

Topic

paulnamroud
Starting Member

26 Posts

Posted - 2012-05-09 : 17:14:37

Hello,

I have defined FullText Search index on the column invoice_number of the table ar_invoice. I'm using SQL Server 2008.

This column contains different values and each value might have special characters such as: '-', '#', '_', '&':

88795411333
887OOO
88996654
889OP
89974
OA-POSTDATED#1
TEST
TEST-GL-CHK
I-0000018
I-0000001-4
I-0000001-5
I-0000012
TEST
C-0000005
I-0000006
345
OA-GL#101AFTER
RECEVEUR GEN
5465465
7897987
99999
C-0000006
I-0000022
I-0000040
OA-879
OA-TESTOA
On-Acct
ON-ACCT
TEST5555
YYYYY
OA-ONACCT-2
AR-1-T-1
AR-1-T-3
AR-1-T-4

I run my procedure and if I try to did the following tests:
- Search for an invoice that contains 000002 => Few records were returned - Perfect!
- Search for an invoice that start with I- => No Records Found - Problem!
- Search for an invoice that start with I-000 => No Records Found - Problem!

and so on ...

So my conclusin is really related to special characters that can be found inside a text ...

Any solution ?

Thank you

Paul

Here's my code:

alter procedure [dbo].[pr_get_ar_invoices_header_list]
(
-- Input parameters
@p_company_code nvarchar(30),
@p_date_from datetime,
@p_date_to datetime,
@p_text nvarchar(300)
) as

Begin

-- -------------------------------------------------------------------------------------------
-- Variable declaration
-- -------------------------------------------------------------------------------------------
Declare @v_text nvarchar(500)
Declare @v_sql nvarchar(4000)

-- -------------------------------------------------------------------------------------------
-- Main program
-- -------------------------------------------------------------------------------------------
If len(rtrim(@p_text)) > 0 --
Begin
Set @v_text = Replace(@p_text, ' ', '*" or "')
Set @v_text = '"' + @v_text + '*"'
End

Else
Begin
Set @v_text = '""'
End -- End If

Select h.*
From ar_invoices h

Where h.invoice_date between @p_date_from and @p_date_to
and contains ( h.invoice_number, @v_text )

End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources