SQL Server-Mysql debugging notes

// Performance
Set statistics io on;
Set statistics time on;

// Mysql splitting string

 

Create procedure proc_split (
Inputstring VARCHAR (1000 ),
Delim CHAR (1)
)
BEGIN
DECLARE strlen INT;
DECLARE last_index INT;
DECLARE cur_index INT;
DECLARE cur_char VARCHAR (200 );
DECLARE len INT;
SET cur_index = 1;
SET last_index = 0;
SET strlen = LENGTH (inputstring );
Drop table if exists splittable;
Create temporary table splittable (
Id INT AUTO_INCREMENT,
Value varchar (20 ),
Primary key ('id '),
Unique key 'id' ('id ')
);
WHILE (cur_index <= strlen) DO
BEGIN
If substring (inputstring FROM cur_index FOR 1) = delim OR cur_index = strlen THEN
SET len = cur_index-last_index-1;
IF cur_index = strlen THEN
SET len = len + 1;
End if;
Insert into splittable ('value') VALUES (SUBSTRING (inputstring FROM (last_index + 1) FOR len ));
SET last_index = cur_index;
End if;
SET cur_index = cur_index + 1;
END;
End while;
END;

CALL proc_split ('1, 2, 3, 4, 3, 4, 4 ',',');
SELECT * from splittable;

// SQL SERVER Segmentation

 

Create function fnSplitStr (
@ SText NVARCHAR (Max ),
@ SDelim CHAR (1)
)

RETURNS @ retArray TABLE (
Value VARCHAR (100)
)
AS
BEGIN
DECLARE
@ PosStart BIGINT,
@ PosNext BIGINT,
@ ValLen BIGINT,
@ SValue NVARCHAR (100 );

IF @ sDelim IS NULL
BEGIN
If len (@ sText)> 100 SET @ sText = SUBSTRING (@ sText, 1,100)

INSERT @ retArray (value)
VALUES (@ sText );
END
ELSE
BEGIN
SET @ posStart = 1;

WHILE @ posStart <= LEN (@ sText)
BEGIN
SET @ posNext = CHARINDEX (@ sDelim, @ sText, @ posStart );

IF @ posNext <= 0
SET @ valLen = LEN (@ sText)-@ posStart + 1;
ELSE
SET @ valLen = @ posNext-@ posStart;

SET @ sValue = SUBSTRING (@ sText, @ posStart, @ valLen );
SET @ posStart = @ posStart + @ valLen + 1;

If len (@ sValue)> 0
BEGIN
If len (@ sValue)> 100 SET @ sValue = SUBSTRING (@ sValue, 1,100)

INSERT @ retArray (value)
VALUES (@ sValue );
END
END
END
RETURN
END

SELECT * FROM fnSplitStr ('1, 2, 3, 2, 3, 3 ',',')