Replacing Multiple Space in SQL

There are multiple ways to remove space. We can remove space from string with help of REPLACE.

For e.g. ‘    This string has            multiple   space.      Replacing multiple        space in SQL   with REPLACE.      ‘

SELECT REPLACE(REPLACE(REPLACE(LTRIM(RTRIM('    This string has            multiple   space.      Replacing multiple        space in SQL   with REPLACE.     ')),'  ',''+CHAR(127)),CHAR(127)+' ',''),CHAR(127),'')

CHAR(127) represent the DEL.

First step is replace two space with space and special character.
In Second step we replace the special character and space to nothing.
In Third step we replace the special character to nothing.

Set Scroll Position of DataGrid/GridView

JavaScript to set the scroll position of DataGrid.

<script language = "javascript">

///This function sets the scroll position of div to cookie.
function setScrollPos()
{
var divY = document.getElementById('div1').scrollTop;
document.cookie = "divPos=!*" + divY + "*!";
}

///Attaching a function on window.onload event.
window.onload = function()
{
var strCook = document.cookie;
if(strCook.indexOf("!~")!=0)
{
var intS = strCook.indexOf("!~");
var intE = strCook.indexOf("~!");
var strPos = strCook.substring(intS+2,intE);
document.body.scrollTop = strPos;
}

/// This condition will set scroll position od <div>.
if(strCook.indexOf("!*")!=0)
{
var intdS = strCook.indexOf("!*");
var intdE = strCook.indexOf("*!");
var strdPos = strCook.substring(intdS+2,intdE);
document.getElementById('div1').scrollTop = strdPos;
}
}

/// Function to set Scroll position of page before postback.
function SetScrollPosition()
{
var intY = document.body.scrollTop;
document.cookie = "yPos=!~" + intY + "~!";
}

/// Attaching   SetScrollPosition() function to window.onscroll event.
window.onscroll = SetScrollPosition;
</script>

In DIV Tag add the onscroll=”setScrollPos();”.

For Example

<DIV id="div1" style="OVERFLOW: auto; WIDTH: 100%; BORDER-TOP-STYLE: none; BORDER-RIGHT-STYLE: none;
BORDER-LEFT-STYLE: none; HEIGHT: 200px; BORDER-BOTTOM-STYLE: none" onscroll="setScrollPos();">

Place DataGrid here...

</DIV>

Watermark Textbox

This is the simple way to make input text box as a watermark text box.
I have put onblur and onfocus event in textbox. See below example.

Search :  <input type="text" onFocus="if (this.value == 'Movies, Actor, Director'){this.value='';this.style.color='Black';this.style.fontStyle='normal';}"
onblur="if (this.value == '') {this.style.color='gray';this.value='Movies, Actor, Director';this.style.fontStyle='normal';} " value='Movies, Actor, Director' style="color:gray;font-style: normal; " />

 

Alphanumeric data sorting using sql query

Hi All,

Today I am representing alphanumeric sorting using sql query.
Sometimes we want to sort data like numeric first and after that all alphanumeric data but sql order by clause doesn’t support this functionality. So I have used replicate function for alphanumeric sorting.

Syntax of Replicate Function:
REPLICATE(character_expression, integer_expression)

character_expression
An alphanumeric expression of character data, or other data types that are implicitly convertible to nvarchar or ntext.
integer_expression
An expression that can be implicitly converted to int. If integer_expression is negative, a null string is returned.

Now comes to the main point how can we use this function in sql for alphanumeric sorting.

See below script

DECLARE @t TABLE(alphanumeric NVARCHAR(50))
INSERT INTO @t VALUES('1')
INSERT INTO @t VALUES('2')
INSERT INTO @t VALUES('1-s')
INSERT INTO @t VALUES('1-d')
INSERT INTO @t VALUES('1-n')
INSERT INTO @t VALUES('v')
INSERT INTO @t VALUES('m')
INSERT INTO @t VALUES('11')
INSERT INTO @t VALUES('12')
INSERT INTO @t VALUES('22')
INSERT INTO @t VALUES('23')

--SELECT alphanumeric FROM @t ORDER BY alphanumeric

SELECT alphanumeric FROM @t ORDER BY
CASE WHEN ISNUMERIC(alphanumeric) = 1 THEN RIGHT(REPLICATE('0',51) + alphanumeric, 50)
WHEN ISNUMERIC(alphanumeric) = 0 THEN LEFT(alphanumeric + REPLICATE('',51), 50)
ELSE alphanumeric
END

Here, I have defined alphanumeric column as NVARCHAR(50) so I have fixed it as following ways
1.    In case of numeric data, I have added 51 zeros in front of the column data and retrieve the last 50 character.
2.    In case of non numeric data, I have added 51 blank spaces at the end of the column data and retrieve the first 50 character.