Percent sign is used any number & any character with LIKE search. Today I needed to search a text which has a percent sign (%)
The text I want to search: “1980%”
SELECT * FROM MY_TABLE WHERE MY_COLUMN LIKE '%1980[%]%' |
One more thing, underscore sign (_) is used to search any “1″ character with LIKE.
SELECT * FROM MY_TABLE_ WHERE MY_COLUMN LIKE '%MEM_T%' |
Returns:
MEMET
MEMATİ
Indexes are useful when reading data. But when inserting data into a table, it may be useful to disable the indexes and rebuild them again after insert operation completed.
Especially when loading huge data manually or with ETL, disable index on pre-sql and rebuild on post-sql:
----Diable Index ALTER INDEX [IX_Index_Name] ON dbo.TABLE_NAME DISABLE |
----Enable Index ALTER INDEX [IX_Index_Name] ON dbo.TABLE_NAME REBUILD |
Bu küçük SQL fonksiyonunu Vergi Kimlik No’ların doğru olup olmadığını sorgulamak için kullanabilirsiniz. Kullanım şekli;
IF dbo.FN_VKNO_DOGRUMU(1111111111)=1 PRINT 'DOGRU VERGI KIMLIK NO' ELSE PRINT 'YANLIS VERGI KIMLIK NO' |
Bu kodları kullanarak rahatlıkla fonksiyonun C# versiyonunu da hazırlayabilirsiniz.
CREATE FUNCTION [dbo].[FN_VKNO_DOGRUMU](@VKNO VARCHAR(10)) RETURNS BIT BEGIN DECLARE @V_LAST_DIGIT SMALLINT, @V_1 SMALLINT, @V_2 SMALLINT, @V_3 SMALLINT, @V_4 SMALLINT, @V_5 SMALLINT, @V_6 SMALLINT, @V_7 SMALLINT, @V_8 SMALLINT, @V_9 SMALLINT, @V_11 SMALLINT, @V_22 SMALLINT, @V_33 SMALLINT, @V_44 SMALLINT, @V_55 SMALLINT, @V_66 SMALLINT, @V_77 SMALLINT, @V_88 SMALLINT, @V_99 SMALLINT, @TOPLAM SMALLINT, @R Bit -- SET @V_1 = (CAST(SUBSTRING(@VKNO,1,1) AS SMALLINT) + 9) % 10 SET @V_2 = (CAST(SUBSTRING(@VKNO,2,1) AS SMALLINT) + 8) % 10 SET @V_3 = (CAST(SUBSTRING(@VKNO,3,1) AS SMALLINT) + 7) % 10 SET @V_4 = (CAST(SUBSTRING(@VKNO,4,1) AS SMALLINT) + 6) % 10 SET @V_5 = (CAST(SUBSTRING(@VKNO,5,1) AS SMALLINT) + 5) % 10 SET @V_6 = (CAST(SUBSTRING(@VKNO,6,1) AS SMALLINT) + 4) % 10 SET @V_7 = (CAST(SUBSTRING(@VKNO,7,1) AS SMALLINT) + 3) % 10 SET @V_8 = (CAST(SUBSTRING(@VKNO,8,1) AS SMALLINT) + 2) % 10 SET @V_9 = (CAST(SUBSTRING(@VKNO,9,1) AS SMALLINT) + 1) % 10 SET @V_LAST_DIGIT = CAST(SUBSTRING(@VKNO,10,1) AS SMALLINT) -- SET @V_11 = (@V_1 * 512) % 9 SET @V_22 = (@V_2 * 256) % 9 SET @V_33 = (@V_3 * 128) % 9 SET @V_44 = (@V_4 * 64) % 9 SET @V_55 = (@V_5 * 32) % 9 SET @V_66 = (@V_6 * 16) % 9 SET @V_77 = (@V_7 * 8) % 9 SET @V_88 = (@V_8 * 4) % 9 SET @V_99 = (@V_9 * 2) % 9 -- IF @V_1 != 0 AND @V_11 = 0 SET @V_11 = 9 IF @V_2 != 0 AND @V_22 = 0 SET @V_22 = 9 IF @V_3 != 0 AND @V_33 = 0 SET @V_33 = 9 IF @V_4 != 0 AND @V_44 = 0 SET @V_44 = 9 IF @V_5 != 0 AND @V_55 = 0 SET @V_55 = 9 IF @V_6 != 0 AND @V_66 = 0 SET @V_66 = 9 IF @V_7 != 0 AND @V_77 = 0 SET @V_77 = 9 IF @V_8 != 0 AND @V_88 = 0 SET @V_88 = 9 IF @V_9 != 0 AND @V_99 = 0 SET @V_99 = 9 -- SET @TOPLAM = @V_11 + @V_22 + @V_33 + @V_44 + @V_55 + @V_66 + @V_77 + @V_88 + @V_99 IF @TOPLAM %10 =0 BEGIN SET @TOPLAM=0 END ELSE BEGIN SET @TOPLAM = (10 - (@TOPLAM %10)) END -- IF @TOPLAM = @V_LAST_DIGIT SET @R = 1 -- DOĞRU ELSE SET @R = 0 -- YANLIŞ -- RETURN @R -- END |
Bu küçük SQL fonksiyonunu TC Kimlik No’ların doğru olup olmadığını sorgulamak için kullanabilirsiniz. Kullanım şekli;
IF dbo.FN_TCNO_DOGRUMU(11111111111)=1 PRINT 'DOGRU TC KIMLIK NO' ELSE PRINT 'YANLIS TC KIMLIK NO' |
Bu kodları kullanarak rahatlıkla fonksiyonun C# versiyonunu da hazırlayabilirsiniz.
CREATE FUNCTION [dbo].[FN_TCNO_DOGRUMU](@TcNo BIGINT) RETURNS BIT AS BEGIN DECLARE @ATCNO BIGINT DECLARE @BTCNO BIGINT DECLARE @C1 Tinyint DECLARE @C2 Tinyint DECLARE @C3 Tinyint DECLARE @C4 Tinyint DECLARE @C5 Tinyint DECLARE @C6 Tinyint DECLARE @C7 Tinyint DECLARE @C8 Tinyint DECLARE @C9 Tinyint DECLARE @Q1 INT DECLARE @Q2 INT DECLARE @R Bit SET @ATCNO = @TcNo / 100 SET @BTCNO = @TcNo / 100 IF LEN(CONVERT(VARCHAR(19),@TcNo)) = 11 BEGIN SET @C1 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @C2 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @C3 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @C4 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @C5 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @C6 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @C7 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @C8 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @C9 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @Q1 = ((10-((((@C1+@C3+@C5+@C7+@C9)*3)+(@C2+@C4+@C6+@C8)) % 10))%10) SET @Q2 = ((10-(((((@C2+@C4+@C6+@C8)+@Q1)*3)+(@C1+@C3+@C5+@C7+@C9))%10))%10) IF (@BTCNO * 100)+(@Q1 * 10)+@Q2 = @TcNo SET @R = 1 ELSE SET @R = 0 END ELSE SET @R = 0 RETURN @R END |
Here you are a very useful script to search a string in all data of tables or views.
You can use this script to find out a parameter stored in which tables.
It is also useful to learn which tables are used for some string in complex repositories as Informatica Repository DB.
USE DB_TO_SEARCH DECLARE @KW VARCHAR(100) = 'STRING_TO_SEARCH', @SQL VARCHAR(MAX) = '' SELECT @SQL += 'IF EXISTS(SELECT * FROM ' + SCHEMA_NAME(t.schema_id) + '.' + T.name + ' WHERE CONVERT(VARCHAR(MAX), ' + c.name + ') LIKE ''%' + @KW + '%'')' + CHAR(10) + 'PRINT ''TABLE NAME:' + SCHEMA_NAME(T.schema_id) + '.' + t.name + '~COLUMN NAME:' + c.name + '''' + CHAR(10) FROM sys.COLUMNS c INNER JOIN sys.TABLES t --Use "sys.views" to seach views instead of tables ON c.object_id = t.object_id INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id AND c.user_type_id = ty.user_type_id WHERE ty.name LIKE '%char%' OR ty.name LIKE '%text%' --You can also change types of columns to search EXEC(@SQL) |
The output of the script as;
TABLE NAME:dbo.OPB_DTL_SWIDG_LOG~COLUMN NAME:LOCATION_NAME
TABLE NAME:dbo.OPB_SWIDGET_ATTR~COLUMN NAME:ATTR_VALUE
Please check the article SQL Script to search objects in all databases to search a string in objects, not data.
When you use (NOLOCK) as below,
SELECT * FROM DWSQL.dw_production.dbo.DW_TARIH (NOLOCK) |
where DWSQL is a linked server, you get the following error:
Msg 4122, Level 16, State 1, Line 1
Remote table-valued function calls are not allowed.
You have to write WITH before (NOLOCK):
SELECT * FROM DWSQL.dw_production.dbo.DW_TARIH WITH (NOLOCK) |
A very simple SQL Select script to search any _TEXT in stored procedures or functions which belongs to given _DB.
SELECT ROUTINE_TYPE, ROUTINE_NAME, ROUTINE_DEFINITION FROM _DB.INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%_TEXT%' AND ROUTINE_TYPE='PROCEDURE' --OR 'FUNCTION' |
Here you are another simple and very useful SQL script to search any object in all databases on the server.
Script searches all tables, views, stored procedures and functions of all databases.
SET NOCOUNT ON DECLARE @DB VARCHAR(40), @CMD VARCHAR(1000), @OBJECT_NAME VARCHAR(100) SET @OBJECT_NAME = 'MIS_MASRKOM_PARAM' DECLARE DBNAME CURSOR FOR SELECT NAME FROM SYS.DATABASES ORDER BY 1 OPEN DBNAME FETCH NEXT FROM DBNAME INTO @DB WHILE @@FETCH_STATUS = 0 BEGIN IF @DB NOT IN ('MASTER', 'MSDB', 'MODEL', 'TEMPDB', 'DECISION_MODULE', 'PUBS', 'NORTHWIND') BEGIN SELECT @CMD = 'USE [' + @DB + '] SELECT DISTINCT ''' + RTRIM(@DB) + ''' AS ' + RTRIM(@DB) + ', SO.NAME, SO.TYPE FROM SYS.SYSOBJECTS SO (NOLOCK) JOIN SYS.SYSCOMMENTS SC (NOLOCK) ON SC.ID=SO.ID WHERE SC.TEXT LIKE ''%' + @OBJECT_NAME + '%'' ORDER BY 2, 3' PRINT RTRIM(@CMD) EXEC(@CMD) END FETCH NEXT FROM DBNAME INTO @DB END DEALLOCATE DBNAME |
Here is a very useful SQL script to search a column in the given database and tables. It will be one of the most valuable script, if you work on many tables.
-- XTYPE (COLUMN TYPES) -- 52 -> SMALLINT -- 56 -> INT -- 48 -> TINYINT -- 127 -> BIGINT -- 106 -> DECIMAL -- 108 -> NUMERIC -- 175 -> CHAR -- 167 -> VARCHAR -- 58 -> SMALLDATETIME -- 61 -> DATETIME SET NOCOUNT ON DECLARE @DB VARCHAR(100), @DB_NAME VARCHAR(100), @CMD VARCHAR(8000), @COLUMN_NAME VARCHAR(500), @TABLE_NAME VARCHAR(500), @COLUMN_TYPE sysname SET @COLUMN_NAME = '%COLUMN_NAME_HERE%' --THE COLUMN YOU WANT TO SEARCH SET @TABLE_NAME = '%TABLE_NAME_HERE%' --LEAVE BLANK TO SEARCH IN ALL TABLES SET @DB_NAME = 'DB_NAME_HERE' SET @COLUMN_TYPE = 'COLUMN_TYPE_HERE' --PLEASE CHECK COLUMN TYPES ABOVE DECLARE DBNAME CURSOR FOR SELECT name FROM sys.DATABASES WHERE database_id > 4 AND name = @DB_NAME ORDER BY 1 OPEN DBNAME FETCH NEXT FROM DBNAME INTO @DB WHILE @@FETCH_STATUS = 0 BEGIN SET @CMD = 'USE [' + @DB + '] SELECT ''' + @DB + ''' AS DB_ADI, t.name AS TABLO_ADI, c.name AS KOLON_ADI, ty.name, c.system_type_id, c.max_length, c.precision, c.collation_name FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id JOIN sys.types ty ON ty.system_type_id = c.system_type_id WHERE t.type = ''U'' AND c.name LIKE ''' + @COLUMN_NAME + ''' AND T.name LIKE ''' + @TABLE_NAME + ''' ORDER BY 2, 3' EXEC(@CMD) FETCH NEXT FROM DBNAME INTO @DB END CLOSE DBNAME DEALLOCATE DBNAME |
