Using CAST or CONVERT in CONTAINS - SQL Server

Question

I'm trying to use the CAST or CONVERT functions in a SQL query like this :

SELECT *
FROM [SQL_BMS].[dbo].[GEID]
WHERE CONTAINS(CONVERT(nvarchar(max), NUID), 'titre')

or

SELECT *
FROM [SQL_BMS].[dbo].[GEID]
WHERE CONTAINS(CAST(NUID as nvarchar(max)), 'titre')

but I'm getting errors:

Incorrect syntax near the keyword 'CONVERT'

or

Incorrect syntax near the keyword 'as'

So how can I cast my NUID column from Int to nvarchar?

Answer 1

You need to create a view with SCHEMABINDING, with some ID and concatenated columns.

CREATE VIEW dbo.[view_GEID]  
WITH SCHEMABINDING  
AS  
SELECT  NUID, 
        Col1+Col2+CAST(Col3 as nvarchar(50))... as ConcatColumn
FROM dbo.GEID

Then create full-text index on that view. Then you can use CONTAINS for search purpose.

SELECT *
FROM [SQL_BMS].[dbo].[view_GEID]
WHERE CONTAINS(ConcatColumn, 'titre')

Another way is to concatenate all columns you need and use LIKE, but it could lead to loss of performance.

SELECT *
FROM [SQL_BMS].[dbo].[GEID]
WHERE Col1+Col2+CAST(Col3 as nvarchar(50))... LIKE '%titre%'