Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 9 November 2012

Fuctions and its Usages..............






      CONCAT (Transact-SQL) SQL2012


STUFF Functions:
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.



        
STUFF ( character_expression , start , length , replaceWith_expression )
       



Script 1:

declare @text varchar(300)

set @text ='Authorities have relaxed a curfew in much of Indian-ruled Kashmir after days of protests'

select stuff(@text,5,24,'') as Result—--------blue lines are replaced with ‘’
Result:
Authurfew in much of Indian-ruled Kashmir after days of protests
Script2:
declare @HTMLText varchar(max)
declare @HTMLText1 varchar(max)
set @htmltext='ser <div id="topcontent" ghjgjhjh>jljgjajajajaljjhjhbhj</div>sfsf <div id="kjljjl"> ssssss<p>'

DECLARE @Start  int
DECLARE @End    int
DECLARE @Length int

SET @Start = CHARINDEX('<div id="topcontent"', @HTMLText)
SET @End = CHARINDEX('</div>', @HTMLText)
SET @Length = (@End - @Start) + 6 ------->length of </div>

select @htmltext 'htmltext'
select @start 'start'
select @end 'end'
select @length as'length'

SET @HTMLText1 = STUFF(@HTMLText, @Start, @Length, '')

select ltrim(rtrim(@HTMLText1)) as result

Result:
ser sfsf <div id="kjljjl"> ssssss<p>




The following example returns a character string created by deleting three characters from the first string, abcdef, starting at position 2, at b, and inserting the second string at the deletion point.
SELECT STUFF('abcdef', 2, 3, 'ijklmn');
GO
Here is the result set.
---------
aijklmnef

(1 row(s) affected)

REPLACE (Transact-SQL):
Replaces all occurrences of a specified string value with another string value.


REPLACE ( string_expression , string_pattern , string_replacement )




The following example replaces the string cde in abcdefghi with xxx.
SELECT REPLACE('abcdefghicde','cde','xxx');
GO
Here is the result set.
------------
abxxxfghixxx
(1 row(s) affected)
The following example uses the COLLATE function.
SELECT    REPLACE('Das ist ein Test'  COLLATE Latin1_General_BIN,
'Test', 'desk' );
GO
Here is the result set.
------------
Das ist ein desk
(1 row(s) affected)

declare @text varchar(300)

set @text ='Authorities have relaxed a curfew in much of Indian-ruled Kashmir after days of protests'

select replace(@text,'a','*') as Result
Result:
*uthorities h*ve rel*xed * curfew in much of Indi*n-ruled K*shmir *fter d*ys of protests



A. Using CONCAT

SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' ) AS Result;
Here is the result set.
Result
-------------------------
Happy Birthday 11/25
 
(1 row(s) affected)

B. Using CONCAT with NULL values

CREATE TABLE #temp (
    emp_name nvarchar(200) NOT NULL,
    emp_middlename nvarchar(200) NULL,
    emp_lastname nvarchar(200) NOT NULL
);
INSERT INTO #temp VALUES( 'Name', NULL, 'Lastname' );
SELECT CONCAT( emp_name, emp_middlename, emp_lastname ) AS Result
FROM #temp;
Here is the result set.
Result
------------------
NameLastname
 
(1 row(s) affected)

CONCAT (Transact-SQL):

Returns a string that is the result of concatenating two or more string values.




         

CONCAT ( string_value1, string_value2 [, string_valueN ] )
       

A. Using CONCAT

SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' ) AS Result;
Here is the result set.
Result
-------------------------
Happy Birthday 11/25
 
(1 row(s) affected)

B. Using CONCAT with NULL values

/*  In Table three columns combined in one column*/


CREATE TABLE #temp (
    emp_name nvarchar(200) NOT NULL,
    emp_middlename nvarchar(200) NULL,
    emp_lastname nvarchar(200) NOT NULL
);
INSERT INTO #temp VALUES( 'Name', NULL, 'Lastname' );
SELECT CONCAT( emp_name, emp_middlename, emp_lastname ) AS Result
FROM #temp;
Here is the result set.
Result
------------------
NameLastname
 
(1 row(s) affected)


PATINDEX (Transact-SQL)

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.




         
PATINDEX ( '%pattern%' , expression )
       



SELECT PATINDEX('%en_ure%', 'please ensure the door is locked');

Result
8

declare @text varchar(300)

set @text ='Authorities have relaxed a curfew in much of Indian-ruled Kashmir after days of protests'

select patindex('%cur_ew%',@text) as Result

Result
28


A. Using a pattern with PATINDEX

The following example finds the position at which the pattern ensure starts in a specific row of the DocumentSummary column in the Document table.
USE AdventureWorks2012;
GO
SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentNode = 0x7B40;
GO 
Here is the result set.
-----------
64
(1 row(s) affected)
If you do not restrict the rows to be searched by using a WHERE clause, the query returns all rows in the table and reports nonzero values for those rows in which the pattern was found, and zero for all rows in which the pattern was not found.

B. Using wildcard characters with PATINDEX

The following example uses % and _ wildcards to find the position at which the pattern 'en', followed by any one character and 'ure' starts in the specified string (index starts at 1):
  SELECT PATINDEX('%en_ure%', 'please ensure the door is locked');
 
Here is the result set.
------------
8
 
PATINDEX works just like LIKE, so you can use any of the wildcards. You do not have to enclose the pattern between percents. PATINDEX('a%', 'abc') returns 1 and PATINDEX('%a', 'cba') returns 3.
Unlike LIKE, PATINDEX returns a position, similar to what CHARINDEX does.

C. Using COLLATE with PATINDEX

The following example uses the COLLATE function to explicitly specify the collation of the expression that is searched.
USE tempdb;
GO
SELECT PATINDEX ( '%ein%', 'Das ist ein Test'  COLLATE Latin1_General_BIN) ;
GO

D. Using a variable to specify the pattern

The following example uses a variable to pass a value to the pattern parameter.
USE AdventureWorks2012; 
GO
DECLARE @MyValue varchar(10) = 'safety'; 
 
SELECT PATINDEX('%' + @MyValue + '%', DocumentSummary) 
FROM Production.Document
WHERE DocumentNode = 0x7B40;
Here is the result set.
------------
22



 
Syntax:
 
CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] ) 
A. Returning the starting position of an expression
The following example returns the position at which the sequence of characters bicycle starts in the DocumentSummary column of the Document table.
USE AdventureWorks2012;
GO
DECLARE @document varchar(64);

SELECT @document = 'Reflectors are vital safety' +
                   ' components of your bicycle.';
SELECT CHARINDEX('bicycle', @document);
GO
Here is the result set.
-----------
48         
B. Searching from a specific position
The following example uses the optional start_location parameter to start looking for vital at the fifth character of the DocumentSummary column.
USE AdventureWorks2012;
GO
DECLARE @document varchar(64);

SELECT @document = 'Reflectors are vital safety' +
                   ' components of your bicycle.';
SELECT CHARINDEX('vital', @document, 5);
GO
Here is the result set.
-----------
16         

(1 row(s) affected)
C. Searching for a nonexistent expression
The following example shows the result set when expressionToFind is not found within expressionToSearch.
USE AdventureWorks2012;
GO
DECLARE @document varchar(64);

SELECT @document = 'Reflectors are vital safety' +
                   ' components of your bicycle.';
SELECT CHARINDEX('bike', @document);
GO
Here is the result set.
-----------
0
(1 row(s) affected)
D. Performing a case-sensitive search
The following example performs a case-sensitive search for the string 'TEST' in 'Das ist ein Test'.
USE tempdb;
GO
--perform a case sensitive search
SELECT CHARINDEX ( 'TEST',
       'Das ist ein Test'
       COLLATE Latin1_General_CS_AS);
Here is the result set.
-----------
0
The following example performs a case-sensitive search for the string 'Test' in 'Das ist ein Test'.

USE tempdb;
GO
SELECT CHARINDEX ( 'Test',
       'Das ist ein Test'
       COLLATE Latin1_General_CS_AS);
Here is the result set.
-----------
13
E. Performing a case-insensitive search
The following example performs a case-insensitive search for the string 'TEST' in 'Das ist ein Test'.

USE tempdb;
GO
SELECT CHARINDEX ( 'TEST',
       'Das ist ein Test'
       COLLATE Latin1_General_CI_AS)

Remarks


If either expressionToFind or expressionToSearch is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type. CHARINDEX cannot be used with text, ntext, and image data types.
If either expressionToFind or expressionToSearch is NULL, CHARINDEX returns NULL.
If expressionToFind is not found within expressionToSearch, CHARINDEX returns 0.
CHARINDEX performs comparisons based on the collation of the input. To perform a comparison in a 

specified collation, you can use COLLATE to apply an explicit collation to the input.
The starting position returned is 1-based, not 0-based.
0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in CHARINDEX.

Real time example:
declare @HTMLText varchar(max)
declare @HTMLText1 varchar(max)
set @htmltext='ser <div id="topcontent" ghjgjhjh>jljgjajajajaljjhjhbhj</div>sfsf <div id="kjljjl"> ssssss<p>'

DECLARE @Start  int
DECLARE @End    int
DECLARE @Length int

SET @Start = CHARINDEX('<div id="topcontent"', @HTMLText)
SET @End = CHARINDEX('</div>', @HTMLText, CHARINDEX('<div id="topcontent"', @HTMLText))
SET @Length = (@End - @Start) + 6 ------->length of </div>


select @htmltext 'htmltext'
select @start 'start'
select @end 'end'
select @length as'length'

SET @HTMLText1 = STUFF(@HTMLText, @Start, @Length, '')
select ltrim(rtrim(@HTMLText1)) as result
Here is the result set.
-----------
ser sfsf <div id="kjljjl"> ssssss<p>
Note:
It removes the <div id=”topcontent”…..</div> only

 Remove the HTML Tags from the table or string???

ALTER FUNCTION [dbo].[udf_StripHTML]
(
@HTMLText varchar(MAX)
)
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @Start  int
DECLARE @End    int
DECLARE @Length int

-- Replace the HTML entity &amp; with the '&' character (this needs to be done first, as
-- '&' might be double encoded as '&amp;amp;')
SET @Start = CHARINDEX('&amp;', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '&')
SET @Start = CHARINDEX('&amp;', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity &lt; with the '<' character
SET @Start = CHARINDEX('&lt;', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '<')
SET @Start = CHARINDEX('&lt;', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity &gt; with the '>' character
SET @Start = CHARINDEX('&gt;', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '>')
SET @Start = CHARINDEX('&gt;', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity &amp; with the '&' character
SET @Start = CHARINDEX('&amp;amp;', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '&')
SET @Start = CHARINDEX('&amp;amp;', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity &nbsp; with the ' ' character
SET @Start = CHARINDEX('&nbsp;', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, ' ')
SET @Start = CHARINDEX('&nbsp;', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
END

-- Replace any <br> tags with a newline
SET @Start = CHARINDEX('<br>', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, CHAR(13) + CHAR(10))
SET @Start = CHARINDEX('<br>', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END

-- Replace any <br/> tags with a newline
SET @Start = CHARINDEX('<br/>', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, 'CHAR(13) + CHAR(10)')
SET @Start = CHARINDEX('<br/>', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END

-- Replace any <br /> tags with a newline
SET @Start = CHARINDEX('<br />', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, 'CHAR(13) + CHAR(10)')
SET @Start = CHARINDEX('<br />', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
END

-- Remove anything between <whatever> tags
SET @Start = CHARINDEX('<', @HTMLText)
SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText))
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')
SET @Start = CHARINDEX('<', @HTMLText)
SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText))
SET @Length = (@End - @Start) + 1
END
RETURN LTRIM(RTRIM(@HTMLText))
END


Uses of the PARSENAME Function:

The PARSENAME() function returns the specified part of an object name.  The parts of an object that can be retrieved are the object name, owner name, database name and server name.  The PARSENAME function does not indicate whether an object by the specified name exists.  PARSENAME just returns the specified part of the specified object name.

PARSENAME ( 'object_name' , object_part )

DECLARE @ObjectName SYSNAME
SET @ObjectName = 'MyServer.SQLServerHelper.dbo.Customer'

SELECT PARSENAME(@ObjectName, 1) AS [ObjectName],
       PARSENAME(@ObjectName, 2) AS [SchemaName],
       PARSENAME(@ObjectName, 3) AS [DatabaseName],
       PARSENAME(@ObjectName, 4) AS [ServerName]

ObjectName  SchemaName  DatabaseName     ServerName
----------- ----------- ---------------- -----------
Customer    dbo         SQLServerHelper  MyServer

Split Full Name Into First Name and Last Name with PARSENAME:

Another use of the PARSENAME function is to split a 2-part full name
into first name and last name.

DECLARE @FullName VARCHAR(50)
SET @FullName = 'Donald Duck'
SELECT PARSENAME(REPLACE(@FullName, ' ', '.'), 2) AS [FirstName],
    PARSENAME(REPLACE(@FullName, ' ', '.'), 1) AS [LastName]
FirstName LastName
----------- ---------- 
Donald Duck

To userid and pwdhashing mechnanism:


---------------For bulk insert/update of password hashing--------------------
update  testdb.dbo.userstabletest  set pwdhash=HashBytes('SHA1', password)
---------For check and update of hashed password include null value also--------------------
update testdb.dbo.userstabletest  set pwdhash=b.[New_Password]
from testdb.dbo.userstabletest u inner join
(
select userid,password,pwdhash,HashBytes('SHA1', password)[New_Password] from 
testdb.dbo.userstabletest
where coalesce(pwdhash,0)<>coalesce(HashBytes('SHA1',password),0)
)as b on u.userid=b.userid and u.password=b.password
Coalesce and Nullif example
 
COALESCE: If expression is NOT NULL then put the left expression here hourly_wage, if NULL then put the right value eg: here zero '0' NULLIF: It compares the two expression is equal or not if equal put null if not equal then put left expression here hourly_wage Script: CREATE TABLE #wages ( emp_id tinyint identity, hourly_wage decimal NULL, salary decimal NULL, ); GO INSERT dbo.#wages (hourly_wage, salary) select 10.00, NULL union select 20.00, NULL union select NULL, 20000.00 union select NULL, NULL SELECT CAST(COALESCE(hourly_wage,0 ) AS int) AS 'coalses Salary Example', nullif(hourly_wage,salary) 'null Salary Example',* FROM dbo.#wages drop table #wages
NULLorEMPTYCHECK UDF function explanation:
 
 
Explanation:
Condition 1:
It replaces if the Category title F,G,J have any value we have to put that value
Condition 2:
but if F,G,J have null or empty we have to put Category E value
Script:
create table #test1(CatTitle_E varchar(10),CatTitle_F varchar(10),CatTitle_G varchar(10), CatTitle_J varchar(10))
insert into #test1
select 'manoj','diwari','ind','x1'
union
select 'ponting',null,null,null
union
select null,null,null,null
union
select 'balaji','','',''
union
select '','','',''
union
select '','watson','shane','aus' 
 
select * from #test1 
 
select CatTitle_E[E],dbo.EmptyorNullcheck(CatTitle_F,CatTitle_E)[rF],CatTitle_F[F],dbo.EmptyorNullcheck(CatTitle_J,CatTitle_E)[rJ],CatTitle_J[J] 
 
from #test1

(OR)

 SELECT  CatTitle_E[E],CatTitle_F[F],CASE ISNULL(CatTitle_F, '')
 
 WHEN '' THEN CatTitle_E
 
 ELSE CatTitle_F
 
 END AS RESULT
 
FROM    #test1

Drop table #test1 
 
 Emptyornullcheck user defined function(UDF):
 
 CREATE FUNCTION dbo.EmptyorNullcheck(@text NVARCHAR(4000),@text1 NVARCHAR(4000)) 
 
RETURNS NVARCHAR(4000) 
 
AS 
BEGIN 
 
declare @Return varchar(300) 
 
IF ISNULL(@text,'')='' or @text is null 
 
begin 
 
select @Return=@text1 
 
end 
 
else if @text is not null 
 
select @Return=@text 
 
return @return 
 
END