Please note that with a little bit changing you can also use this query as a stored procedure.
Tag: find
How to find the first missing value in a series in MS SQL
Let’s say we have this values in the database: num — 1 2 4 5 6 8 9 11 To Find the first missing value, we can use this: ;WITH CteRN AS( SELECT *, RN = num – ROW_NUMBER() OVER(ORDER BY num) FROM tbl ) SELECT TOP 1 num – RN FROM CteRN WHERE RN […]
How to find repetitive records and keep only 1 record for uniqueness in SQL
–STEP 1: Find Repetitive Records (Only the Active Ones) SELECT INDEX1, INDEX2 INTO #TEMP_TABLE FROM MY_TABLE WHERE ACTIVE = 1 GROUP BY INDEX1, INDEX2 HAVING COUNT(1) > 1 ORDER BY COUNT(1) DESC –STEP 2: Create the Cursor DECLARE @INDEX1 INT DECLARE @INDEX2 INT DECLARE @ID INT DECLARE MY_CURSOR CURSOR FOR SELECT INDEX1, INDEX2 FROM #TEMP_TABLE […]
SQL Query to Find and Replace text in a stored procedures
Declare @spnames CURSOR Declare @spname nvarchar(max) Declare @moddef nvarchar(max) Set @spnames = CURSOR FOR select distinct object_name(c.id) from syscomments c, sysobjects o where c.text like ‘%findtext%’ and c.id = o.id and o.type = ‘P’ OPEN @spnames FETCH NEXT FROM @spnames into @spname WHILE @@FETCH_STATUS = 0 BEGIN Set @moddef = (SELECT Replace ((REPLACE(definition,’findtext’,’replacetext’)),’ALTER’,’create’) FROM sys.sql_modules […]
Find numbers with more than two decimal places in SQL
You can use SELECT query below to find numbers with more than two decimal places in SQL: