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.

Syntax

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>

Examples

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');

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.

Syntax

REPLACE ( string_expression , string_pattern , string_replacement )

Examples

The following example replaces the string cde in abcdefghi with xxx.

SELECT REPLACE('abcdefghicde','cde','xxx');

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' );

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

Examples

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.

Syntax

CONCAT ( string_value1, string_value2 [, string_valueN ] )

Examples

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.

Syntax

PATINDEX ( '%pattern%' , expression )

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

Result

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

Examples

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.
------------

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;

DECLARE @document varchar(64);

SELECT @document = 'Reflectors are vital safety' +

' components of your bicycle.';

SELECT CHARINDEX('bicycle', @document);

Here is the result set.

-----------

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;

DECLARE @document varchar(64);

SELECT @document = 'Reflectors are vital safety' +

' components of your bicycle.';

SELECT CHARINDEX('vital', @document, 5);

Here is the result set.

-----------

(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;

DECLARE @document varchar(64);

SELECT @document = 'Reflectors are vital safety' +

' components of your bicycle.';

SELECT CHARINDEX('bike', @document);

Here is the result set.

-----------

(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;

--perform a case sensitive search

SELECT CHARINDEX ( 'TEST',

'Das ist ein Test'

COLLATE Latin1_General_CS_AS);

Here is the result set.

-----------

The following example performs a case-sensitive search for the string 'Test' in 'Das ist ein Test'.

USE tempdb;

SELECT CHARINDEX ( 'Test',

'Das ist ein Test'

COLLATE Latin1_General_CS_AS);

Here is the result set.

-----------

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;

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

Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday 9 November 2012

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

A. Using CONCAT

B. Using CONCAT with NULL values

CONCAT (Transact-SQL):

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

A. Using CONCAT

B. Using CONCAT with NULL values

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

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.

A. Using a pattern with PATINDEX

B. Using wildcard characters with PATINDEX

C. Using COLLATE with PATINDEX

D. Using a variable to specify the pattern

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

No comments:

Post a Comment

Recent Comments on this Blog

SQL Question

Visitors Location @ Globe

Featured post

How to Verify Database Backup file is not corrupted one in SQL Server? How to Validate Database Backup in SQL Serer?