Tuesday, 17 July 2018

Sql Server REPLACE


Replace searches for certain characters in a string and replaces  with mentioned characters.

Syntax :
 
REPLACE(source String, string_to_replace, string_for_replacement


source string : The source string.
string_to_replace : The string to search to replace.
string for replacement :
The string to be come in place of string_to_replace.

Here is the example of REPLACE. 

Example 1

SELECT REPLACE('Hellow World','ll', 'LL')
Result: HeLLow World


Here is the example of REPLACE with update statement.  

Example 2

UPDATE student
SET adress = REPLACE(adress, 'City', 'LOCALITY')
WHERE city='MyCity%'


Sql Server SUBSTRING


SUBSTRING a return part of a string. SUBSTRING is used to get a part of a  string from the original string. SUBSTRING not only work on string or text data, SUBSTRING work on binary or image data also. SUBSTRING always return a part of string , text , binary or image data which was supplied to it.Here is the list of data type that support SUBSTRING

1)char2)varchar3)text4)nchar5)nvarchar6)ntext7)binary8)varbinary9)image
If you supply a binary data type to the SUBSTRING, SUBSTRINGreturn binary, if you supply sting, SUBSTRINGwill return string. The same scenario happen for text , image data also.

Syntax :

SUBSTRING ( stringn ,start_position , length ) 

string : The source string on which SUBSTRING will be applied.
start_position :  The position , from where start extraction.
length : The number of characters to be extracted. 


Below are the example of SUBSTRING from different position to different length. The data supplied is string and return also string.


Example 1

SELECT SUBSTRING('Hellow World',8,5)
Result: World

Example 2 

SELECT SUBSTRING('Hellow World',0,6)
Result: Hello

Example 3 

SELECT SUBSTRING('Hellow World',7,1)
Result: ''

Monday, 16 July 2018

Sql Server LEN



LEN in function is used to get the length . LEN function always return numeric values as length. Length function works on null , space , even number.LEN function do not consider the ending spaces ,but is consider starting space.


Here is the result,LEN function return length of character.
Example 1

SELECT LEN('Hello World!');
Result: 12


Here is the result,LEN function does not consider ending space.
Example 2

SELECT LEN('Hello World! ');
Result: 12 (trailing spaces are not included in the calculation)


Here is the result,LEN function consider starting space.
Example 3
SELECT LEN(' Hello World!');
Result: 15


Here is the result,LEN function consider starting space but not ending space.
Example 4
SELECT LEN(' Hello World! ');
Result: 15


Here is the result,LEN function does not consider ending space.
Example 5
SELECT LEN(' ');
Result: 0

Here is the result,LEN function does not consider ending space.
Example 6
SELECT LEN('');
Result: 0


Example 7
SELECT LEN(NULL);
Result: NULL

Sql Server ROW_NUMBER

ROW_NUMBER number generate output automatically in the query output a sequential number in each row separated by partition.Every partition has separate row number as per order by clause. You can use ROW_NUMBER both ways without or with partition by. 

We have created a table and corresponding data to do some sample example.

CREATE TABLE student
(
       id INT NOT NULL,
       name VARCHAR (500) NOT NULL,
       age INT NOT NULL,
       adress VARCHAR (500) NOT NULL,
       date_of_birth DATETIME NOT NULL,
       height DECIMAL(10,2) NOT NULL
)


INSERT INTO student(id,name,age,adress,date_of_birth,height)
SELECT 1,'John1',12,'34,student street','01/08/2018',10.5
UNION
SELECT 1,'John2',13,'35,student street','02/08/2018',11
UNION
SELECT 1,'John3',12,'36,student street','03/08/2018',11.5
UNION
SELECT 1,'John4',13,'37,student street','04/08/2018',12
UNION
SELECT 1,'John5',12,'38,student street','05/08/2018',12.5
UNION
SELECT 1,'John6',13,'39,student street','06/08/2018',13.5
UNION
SELECT 1,'John7',15,'40,student street','07/08/2018',14
UNION
SELECT 1,'John8',14,'41,student street','08/08/2018',14.5
UNION
SELECT 1,'John9',12,'42,student street','09/08/2018',15
UNION
SELECT 1,'John10',15,'43,student street','10/08/2018',15.5
UNION
SELECT 1,'John11',16,'44,student street','11/08/2018',16

1)Here is example of two number without  partition by
 
SELECT name,
       age, 
       adress,
       date_of_birth,
       height , 
       row_number() over (order by name) as RowNum 
       FROM student

Output
name    age   adress            date_of_birth         height  RowNum   
John1   12    34,student street    2018-01-08 00:00:00.000    10.50    1
John10  15    43,student street    2018-10-08 00:00:00.000    15.50    2
John11  16    44,student street    2018-11-08 00:00:00.000    16.00    3
John2   13    35,student street    2018-02-08 00:00:00.000    11.00    4
John3   12    36,student street    2018-03-08 00:00:00.000    11.50    5
John4   13    37,student street    2018-04-08 00:00:00.000    12.00    6
John5   12    38,student street    2018-05-08 00:00:00.000    12.50    7
John6   13    39,student street    2018-06-08 00:00:00.000    13.50    8
John7   15    40,student street    2018-07-08 00:00:00.000    14.00    9
John8   14    41,student street    2018-08-08 00:00:00.000    14.50    10
John9   12    42,student street    2018-09-08 00:00:00.000    15.00    11



2)Here is the example of row number with partition by 

SELECT name, 
       age,
       adress,
       date_of_birth,height , 
       row_number() OVER(PARTITION BY name ORDER BY age ASC)
FROM student

Output
name    age   adress            date_of_birth         height  RowNum   
John1    12    34,student street    2018-01-08 00:00:00.000    10.50    1
John10    15    43,student street    2018-10-08 00:00:00.000    15.50    1
John11    16    44,student street    2018-11-08 00:00:00.000    16.00    1
John2    13    35,student street    2018-02-08 00:00:00.000    11.00    1
John3    12    36,student street    2018-03-08 00:00:00.000    11.50    1
John4    13    37,student street    2018-04-08 00:00:00.000    12.00    1
John5    12    38,student street    2018-05-08 00:00:00.000    12.50    1
John6    13    39,student street    2018-06-08 00:00:00.000    13.50    1
John7    15    40,student street    2018-07-08 00:00:00.000    14.00    1
John8    14    41,student street    2018-08-08 00:00:00.000    14.50    1
John9    12    42,student street    2018-09-08 00:00:00.000    15.00    1

বাঙালির বেড়ানো সেরা চারটি ঠিকানা

  বাঙালি মানে ঘোড়া পাগল | দু একদিন ছুটি পেলো মানে বাঙালি চলল ঘুরতে | সে সমুদ্রই হোক , পাহাড়ি হোক বা নদী হোক। বাঙালির ...