Find an item from database according to char index

Hi ,

I have found the another way to find an item from SQL Server according to the position of character.
There are many ways to do it.

One way to do is use 'like' with '%' operator
Second way to do is  use 'like' with '_' operator
Third way to do is use charIndex function of SQL Server

Suppose you have find a record from database whose length should not greater then 12 and first char should be 'T' and last char should 'Z'.

In this case you cannot use like with %  because result will not depends upon length of parameter provided.

I would suggest to try this with your table and see the results you get. It will also give you a little learning.


SELECT  * FROM 
                tblData
WHERE 
                DisplayText like 'T%Z'



SELECT  * FROM 
                tblData
WHERE 
                DisplayText like 'T__________Z'

Get Updated Row Data

I never knew that such kind of things are there in SQL Server. Its amazing every person who works on SQL should know that. Its awesome.

 

Try those below queries with your table and columns 

/****** Script for SelectTopNRows command from SSMS  ******/

SELECT TOP 1000 [ID]

      ,[tblname]

      ,[UpdateTime]

  FROM [SQLToLinq].[dbo].[tblMNames]

  

  UPDATE 

                 tblMNames

  SET 

                 tblname='tblMnames'

  OUTPUT 

                INSERTED.UpdateTime , INSERTED.tblname

  WHERE 

                  tblname='tblMName'

SQL Server full text index

From full text index we can enhance the performance of SQL queries which you can directly see on SQL actual plan execution.

Documents supported for full text index :-

SELECT document_type, version, manufacturer
FROM sys.fulltext_document_types

Language supported for full text :-

SELECT * FROM sys.fulltext_languages

ORDER BY lcid

Already existed full text catalog in Database :-

SELECT fulltext_catalog_id, name 

FROM sys.fulltext_catalogs

Table and Full Text Catalog relation :-

SELECT t.name AS TableName, c.name AS FTCatalogName

FROM sys.tables t JOIN sys.fulltext_indexes i

  ON t.object_id = i.object_id

JOIN sys.fulltext_catalogs c

  ON i.fulltext_catalog_id = c.fulltext_catalog_id

Creation of Full text Index on table column :-

--!!! Drop Full Text Catalogue !!!--
IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[tblData]'))
ALTER FULLTEXT INDEX ON [dbo].[tblData] DISABLE
GO

/****** Object:  FullTextIndex     Script Date: 06/13/2013 23:11:07 ******/
IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[tblData]'))
DROP FULLTEXT INDEX ON [dbo].[tblData]
GO
-------------------------------------------------------------------------------------------------------------------
--!!! Add a new column !!!--
IF NOT EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS  
  WHERE TABLE_NAME = 'tblData'
  AND COLUMN_NAME = 'Records') 
BEGIN
ALTER TABLE dbo.tblData ADD
Records nvarchar(450) NULL
END
GO
-------------------------------------------------------------------------------------------------------------------

---!!! Update the Records column with Values !!!---
UPDATE dbo.tblData SET [Records]=FirstName +' | ' LastName
GO
-------------------------------------------------------------------------------------------------------------------

---!!! Add index on DisplayUserText Column !!!---
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_Users_Records' AND object_id = OBJECT_ID('tblData'))
BEGIN
CREATE NONCLUSTERED INDEX [IX_Users_Records] ON [dbo].[tblData] 
(
[Records] ASC
)
WHERE ([Records] IS NOT NULL)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END
GO
------------------------------------------------------------------------------------------------------------------
---!!! Create full text catalog !!!---
IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE [name] = 'FTC_tblData')
BEGIN
CREATE FULLTEXT CATALOG [FTC_tblData] WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION [dbo]
END
GO
------------------------------------------------------------------------------------------------------------------
---!!! Create Stoplist !!!---
IF NOT EXISTS (SELECT Name FROM sys.fulltext_stoplists WHERE [name] = 'FTS_tblData')
CREATE FULLTEXT STOPLIST FTS_tblData FROM SYSTEM STOPLIST;
GO
------------------------------------------------------------------------------------------------------------------
--!!! Create Full text index !!!--
IF NOT EXISTS (SELECT * FROM sys.fulltext_indexes WHERE object_id = object_id('tblData'))
BEGIN
 CREATE FULLTEXT INDEX ON DBO.tblData([FirstName] LANGUAGE 0, [LastName] LANGUAGE 0)
 KEY INDEX PK_tblData ON FTC_tblData
    WITH STOPLIST = [FTS_tblData];
END
GO

---!!! Now we are done with full text index !!!---
(For more details visit this URL : https://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/)

How to use :-

SELECT *
FROM tblUsers
WHERE CONTAINS(FirstName,'"*Jes*"');