How to split string value into Integer

How to split string value into Integer

--==================================================================

-- how to split string value in to int using delimmiter (you can do these by creating TVF-function.

--==================================================================

CREATE FUNCTION [dbo].[SplitStringReturnInt]

(      @string varchar(max)

       ,@delimiter varchar(10)

)

RETURNS

@return TABLE (id int)

AS

BEGIN

       SET @string = RTRIM(LTRIM(@delimiter)) + RTRIM(LTRIM(@string)) + RTRIM(LTRIM(@delimiter))

       DECLARE @x bit = 1

      

       ;WITH E1(N)

       AS

       (      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

              UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

       ), E2(N)

       AS

       (      SELECT 1

              FROM E1 a, E1 b

       ), E4(N)

       AS

       (      SELECT ROW_NUMBER() OVER(ORDER BY @x)

              FROM E2 a, E2 b, E2 c

       )

       INSERT INTO @return

    SELECT    CASE ISNUMERIC(SUBSTRING(@string, N + 1, CHARINDEX(@delimiter, @string, N + 1) - N -1))

                           WHEN 1 THEN SUBSTRING(@string, N + 1, CHARINDEX(@delimiter, @string, N + 1) - N -1)

                     END

    FROM E4

    WHERE N <= LEN(@string) - 1

              AND SUBSTRING(@string, N, 1) = @delimiter

      

       RETURN

END

-- Now Lets Test these function.

Declare @stre varchar(max) = ('1234567891,234234,23424,2342,324234,2,4234,23423,243634,345435,53453')

SELECT * FROM [dbo].[SplitStringReturnInt](@stre,',')

Declare @stre varchar(max) = ('5345.53453.3453534.345345.3453453.435345.3')

SELECT * FROM [dbo].[SplitStringReturnInt](@stre,'.')

SQL SERVER CURSORS

CURSORS : - In Simple words, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis,

--======================================================

-- SINGLE  VARIABLE AS INPUT TO EXEC PROCEDURE which takes only one variable as input

--======================================================

DECLARE @CLIENTKEY INT           

DECLARE CLIENTKEYCURSOR CURSOR

              FOR SELECT ID FROM customer 

OPEN CLIENTKEYCURSOR                                    -- open cursor

                                                       -- DO SOMETHING USEFUL

       FETCH NEXT FROM CLIENTKEYCURSOR                 -- fetch record

       INTO @CLIENTKEY

       WHILE @@FETCH_STATUS = 0

              BEGIN 

                     FETCH NEXT FROM CLIENTKEYCURSOR   -- loop through every record

                           INTO @CLIENTKEY

                           EXEC dbo.uspUpdateClientDate @CLIENTKEY

                           --SELECT   @CLIENTKEY

               END         

CLOSE CLIENTKEYCURSOR                                  -- close cursor

DEALLOCATE CLIENTKEYCURSOR                             -- deallocate cursor

--======================================================

---- MORE THAN ONE VARIABLE AS INPUT TO EXEC PROCEDURE which takes three variable as input

--======================================================

DECLARE @ID INT,@NAME VARCHAR(100), @AD VARCHAR(100)

DECLARE  NAMECURSOR CURSOR

               FOR SELECT ID, NAME, [ADD] FROM CUSTOMER

OPEN NAMECURSOR

              FETCH NEXT FROM NAMECURSOR

                     INTO @ID, @NAME, @AD

       WHILE @@FETCH_STATUS = 0

       BEGIN

              FETCH NEXT FROM NAMECURSOR

                     INTO @ID, @NAME, @AD

           EXEC [dbo].[UspInsertCustomerDetails] @ID, @NAME, @AD

       END

CLOSE NAMECURSOR

DEALLOCATE NAMECURSOR

SQL FUNCTIONS

-- SQL FUNCTION

--Returns the right part of a character string with the specified number of characters

SELECT RIGHT('SQLDBA567',3)

--Returns the left part of a character string with the specified number of characters.

SELECT LEFT('SQLDBA567',6)

--Returns the number of characters of the specified string expression, excluding trailing blanks.

SELECT LEN('SQLDBA567')

--Replaces all occurrences of a specified string value with another string value.

SELECT REPLACE('S2Q2L2D2B_2A252627','2','')

---Searches an expression for another expression and returns its starting position if found.

SELECT charindex('A',  'SQLDBA567' )  

--Returns the starting position of the first occurrence of a pattern in a specified expression,

--or zeros if the pattern is not found, on all valid text and character data types.

SELECT PATINDEX ('%DB%', 'SQLDBA567')

--Returns part of a character, binary, text, or image expression in SQL Server.

SELECT SUBSTRING('SQLDBA567-Developer', 4,3)

--The STUFF function inserts a string into another string. It deletes a specified length of characters in the

--first string at the start position and then inserts the second string into the first string at the start position.

SELECT STUFF ('SQLDevDBA', 4,3,'')

--Returns a character expression after converting uppercase character data to lowercase.

SELECT LOWER('SQLDBA') 

--Returns a character expression with lowercase character data converted to uppercase.

SELECT UPPER ('sqldba') 

--Returns a character expression after it removes leading blanks.

SELECT LTRIM('      SQLDBA567   ') 

--Returns a character string after truncating all trailing blanks.

SELECT RTRIM('     SQLDBA567   ') 

--Returns character data converted from numeric data.

SELECT STR(12345.6789, 6, 1);

DECLARE @TABLE TABLE

(COL1 INT)

INSERT INTO @TABLE VALUES (15),(20),(30)

SELECT * FROM @TABLE

SELECT AVG(COL1) AS AVERAGE FROM @TABLE     --Gives avgerage for that COL1

SELECT MIN(COL1) AS MINIMUM FROM @TABLE     --Gives minimum value for that COL1

SELECT MAX(COL1) AS MAXIMUM FROM @TABLE     --Gives maximum value for that COL1

SELECT SUM(COL1) AS TOTAL FROM @TABLE       --Gives Total sum value for that COL1

SELECT COUNT(COL1)  TOTAL_COUNT FROM @TABLE --Gives number of row in that COL1

SELECT COUNT(COL1)  TOTAL_COUNT FROM @TABLE

--DIFFERENCE BETWEEN ISNULL AND COALESCE

DECLARE @TABLE table

(Col VARCHAR(100))

INSERT INTO @TABLE VALUES (NULL),('SECOND VALUE')

SELECT * FROM @TABLE

SELECT ISNULL (COL, 'DEFAULT VALUE') AS VALUE  FROM @TABLE

DECLARE @TABLE TABLE

(COL1 VARCHAR(25), COL2 VARCHAR(25), COL3 VARCHAR(25))

INSERT INTO @TABLE VALUES (NULL, NULL,'THIRD VALUE'), ('FIRST VALUE', NULL, NULL), (NULL,'SECOND VALUE', NULL)

SELECT * FROM @TABLE

SELECT COALESCE (COL1, COL2, COL3) FROM @TABLE

--WHAT IS RANK(), ROW_NUMBER(), DENSE_RANK(), NTILE() WITH E.G

WITH TABLE1 (CHECK_ID, ID_NO)

     AS (SELECT 7,3 UNION ALL

         SELECT 7,7 UNION ALL

         SELECT 7,7 UNION ALL

               SELECT 7,9 UNION ALL

         SELECT 7,9)

SELECT *,

       RANK() OVER(PARTITION BY CHECK_ID ORDER BY ID_NO)       AS 'RANK',

       ROW_NUMBER() OVER(PARTITION BY CHECK_ID ORDER BY ID_NO) AS 'ROW_NUMBER',

       DENSE_RANK() OVER(PARTITION BY CHECK_ID ORDER BY ID_NO) AS 'DENSE_RANK',

          NTILE(2) OVER(PARTITION BY CHECK_ID ORDER BY ID_NO) AS 'Ntile'

FROM   TABLE1

What is Primary Key, Foreign Key, Candidate Key, Alternative Key, Composite Key

Different keys in SQL SERVER

Primary key: Primary key uniquely identify a record in the table. Primary Key can't accept null values. By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index, We can have only one Primary key in a table.

Foreign key: Foreign key is a field in the table that is referenced to primary key in another table, Foreign key can accept multiple null value,

Foreign key do not automatically create an index, you can manually create an index on foreign key, We can have more than one foreign key in a table.

Candidate key: A column, or set of columns that can uniquely identify a row in a table.

Alternate key: Any candidate key that has not been selected as the primary key.

Composite key: A key that is composed of more than one column.

How to check latest statistics updated date in SQL SERVER

How to check latest statistics updated date in sql server

SELECT SCHEMA_NAME(schema_id) AS SchemaName

      , OBJECT_NAME(o.object_id)  AS ObjectName

      ,type  AS ObjectType

      ,s.name  AS StatsName

      , STATS_DATE(o.object_id, stats_id) AS StatsDate

FROM sys.stats s INNER JOIN sys.objects o ON o.object_id=s.object_id

WHERE OBJECTPROPERTY(o.object_id, N'ISMSShipped') = 0

AND LEFT(s.Name, 4) != '_WA_'

ORDER BY ObjectType, SchemaName, ObjectName, StatsName;

How to change database to single user mode

HOW TO CHANGE DATABASE TO SINGLE USER MODE

ALTER DATABASE [YOUR_DATABASENAME]

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

-- WITH NO WAIT WILL ONLY SET THE DATABASE TO SINGLE USER MODE IF ALL TRANSACTION HAVE BEEN COMPLETED.IT WILL NOT WORK IF THERE IS OPEN TRANSACTION

ALTER DATABASE [YOUR_DATABASENAME]

SET SINGLE_USER WITH NO_WAIT

GO

-- HOW TO CHANGE DATABASE TO MULTIPLE USER MODE

 ALTER DATABASE [YOUR_DATABASENAME]

 SET MULTI_USER WITH ROLLBACK IMMEDIATE

 GO

-- HOW TO CHANGE DATABASE TO READ ONLY

ALTER DATABASE  [YOUR_DATABASENAME]

SET    READ_ONLY

-- HOW TO CHANGE DATABASE TO READ WRITE

ALTER DATABASE  [YOUR_DATABASENAME]

SET    READ_WRITE

How to check Sql Server Start Time

--QUERY - How to check SQL SERVER START TIME

SELECT SQLSERVER_START_TIME FROM SYS.DM_OS_SYS_INFO

How to check database size

  -- HOW TO GET DATABASE SIZE IN INSTANCE LEVEL IN MEGABITES

SELECT DB_NAME(DBID),

            STR(CONVERT (DEC(15), SUM(SIZE)) * 8192 / 1048576, 10,2) + N' mb' AS DATABASE_SIZE

FROM sys.sysaltfiles

GROUP BY DBID ORDER BY 2 DESC