Menu ▼


In SQL server there is no inbuilt trim function, but we have inbuilt LTRIM() & RTRIM() functions. LTRIM() function will trim the left side (leading)spaces and RTRIM() function will remove right side(trailing) spaces in a string. So by using inbuilt LTRIM() & RTRIM() function we can write the user define TRIM() function to trim the leading and trailing spaces in a string.

Function

CREATE FUNCTION TRIM(@inputStr NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
BEGIN
    RETURN LTRIM(RTRIM(@inputStr))
END

Usage

SELECT dbo.TRIM(' Hello World                                        ')
Output
'Hello World'

Using in SELECT statement

SELECT dbo.TRIM(EmployeeName) FROM tblEmployee

Read More...
Submited By : mrkraju On 03 Dec 2015

SQL string REVERSE() function was introduced in SQL Server 2008. REVERSE() function will returns the reverse order of a string value. If you given integer value or datetime to reverse() function it will first convert to string value and then returns the reverse value of it.

Example

SELECT REVERSE('HELLO WORLD')
Output
DLROW OLLEH


Example

SELECT REVERSE('56789')
Output
98765


Example

SELECT REVERSE(GETDATE())
Output
MP74:01 5102 2 ceD


Example

Read More...
Submited By : mrkraju On 02 Dec 2015

By using regularexpesssion and PatIndex we can remove the non alphabetic characters from a string in sql server. Below sql function will take the input string and remove the special characters and non alphabets and returns the output string.

SQL function to remove non alphabets

CREATE Function [dbo].[RemoveNonAlphabets](@input VarChar(4000))
Returns VarChar(4000)
AS
Begin

    Declare @Alphabets as varchar(50)
    Set @Alphabets = '%[^a-z]%'
    While PatIndex(@Alphabets, @input) > 0
        Set @input = Stuff(@input, PatIndex(@Alphabets, @input), 1, '')

    Return @input
End

Example
SELECT dbo.RemoveNonAlphabets ('Hello 123 Welcome!')
Output
Hello Welcome

Note
If you dont want to remove the spaces then add a space in the regularexpesssion after z and before ] as below
Set @Alphabets = '%[^a-z ]%'

Read More...
Submited By : mrkraju On 21 Nov 2015

Below SQL function will remove the non alphabet characters from the given input value.

CREATE Function [dbo].[RemoveNonAlphabetCharacters](@Val VarChar(1000))
Returns VarChar(1000)
AS
Begin
    Declare @RegExp as varchar(50)
    Set @RegExp = '%[^a-z]%'
    While PatIndex(@RegExp, @Val) > 0
        Set @Val = Stuff(@Val, PatIndex(@RegExp, @Val), 1, '')    
    Return @Val
End

Example:
SELECT dbo.RemoveNonAlphabetCharacters('Hello 123, How are you?')
Output:
HelloHowareyou
Read More...
Submited By : mrkraju On 01 Sep 2015

Below SQL function will remove the non numeric characters from the given input value.

CREATE Function [dbo].[RemoveNonNumaricCharacters](@Val VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @RegExp as varchar(50)
    Set @RegExp = '%[^0-9]%'
    While PatIndex(@RegExp, @Val) > 0
        Set @Val = Stuff(@Val, PatIndex(@RegExp, @Val), 1, '')    
    Return @Val
End

Example:
SELECT dbo.RemoveNonNumaricCharacters('Hello 123')
Output:
123
Read More...
Submited By : mrkraju On 01 Sep 2015

CREATE FUNCTION [dbo].[fnSplit](@input NVARCHAR(MAX), @separator NVARCHAR(10))

RETURNS @tbl TABLE (Value NVARCHAR(max))

AS

BEGIN

   

    DECLARE @xml XML;

    SELECT    @xml = CAST('<input>' + REPLACE(@input, @separator, '</input><input>') + '</input>' AS XML);

 

    INSERT INTO @tbl(Value)

    SELECT  Temp.split.value('.', 'NVARCHAR(max)') AS Value

    FROM    @xml.nodes('/input') Temp(split)

   

    RETURN

END

 

Output


Read More...
Submited By : mrkraju On 21 Nov 2013




Search dotnetlearners.com