SQL FUNCTION ExtractNumbers

SQL FUNCTION ExtractNumbers

Create Function ExtractNumbers

(

@str_passed varchar(max)

)

Returns varchar(50)

AS

BEGIN

    declare @val varchar(50), @i int

    select @val = '', @i = 1

    while (@i <= len(@ str_passed))

        select @val= @val+ (CASE when substring(@ str_passed,@i,1) like '[0-9]' 

                                  then substring(@ str_passed,@i,1) else '' END),

              SET @i = @i + 1

    Return @val

END

Usage:-

SELECT SrNo,  dbo. ExtractNumbers(AlphaNumCol) as NumColData from TableName

Drop commas from '45,22,135'

or Drop dash & braces from '(91)123-456-789'

or Drop alphabets from 'C99B33A1234'

Create A Comma Delimited List From a Column

Create A Comma Delimited List From a Column

Create Sample Data for Both Solutions

-- Suppress data loading messages SET NOCOUNT ON   -- Create Sample Data  CREATE TABLE TeamInfo    (  MemberID    int IDENTITY,       TeamID      int,        FirstName   varchar(50)    )   -- Load Sample Data INSERT INTO TeamInfo VALUES ( 1, 'Jim' ) INSERT INTO TeamInfo VALUES ( 1, 'Mary' ) INSERT INTO TeamInfo VALUES ( 1, 'Bob' ) INSERT INTO TeamInfo VALUES ( 2, 'Sue' ) INSERT INTO TeamInfo VALUES ( 2, 'Ralph' ) INSERT INTO TeamInfo VALUES ( 2, 'Ellen' ) INSERT INTO TeamInfo VALUES ( 3, 'Bill' ) INSERT INTO TeamInfo VALUES ( 3, 'Linda' )

create function -- SQL 2000, Retrieve desired data

-- With SQL 2000, we will create a User Defined Function to do the concatenation. -- While this solution can also be used with SQL Server 2005/SQL Server 2008,  -- the previous suggestion is more efficient.   CREATE FUNCTION dbo.fnMakeTeamList    (  @TeamID int  )    RETURNS varchar(1000) AS    BEGIN       DECLARE @TempTeam table          (  Firstname varchar(20)  )       DECLARE @TeamList varchar(1000)       SET @TeamList = ''       INSERT INTO @TempTeam          SELECT FirstName          FROM TeamInfo          WHERE TeamID = @TeamID       IF @@ROWCOUNT > 0          UPDATE @TempTeam             SET @TeamList = ( @TeamList + FirstName + ', ' )       RETURN substring( @TeamList, 1, ( len( @TeamList ) - 1 ))    END   -- Usage SELECT     TeamID,     MemberList = dbo.fnMakeTeamList( TeamId )  FROM TeamInfo  GROUP BY TeamID   -- Results TeamID     MemberList 1 Jim, Mary, Bob 2 Sue, Ralph, Ellen 3 Bill, LindaS

QL Server 2005 / SQL Server 2008 Solution

--Retrieve desired data SELECT    t1.TeamID,    MemberList = substring((SELECT ( ', ' + FirstName )                            FROM TeamInfo t2                            WHERE t1.TeamID = t2.TeamID                            ORDER BY                                TeamID,                               FirstName                            FOR XML PATH( '' )                           ), 3, 1000 )FROM TeamInfo t1 GROUP BY TeamID   -- Results TeamID     MemberList 1 Bob, Jim, Mary 2 Ellen, Ralph, Sue 3 Bill, Linda

SQL2005 String functions

SQL 2005 String Functions

String Functions are used for manipulating string expression. 

(Pass strings using single quotes)

Len('') - Returns length of string.

select Len("Urgent Work") will return 11

Lower('') - Convert all characters to lowercase characters.

select Lower('Urgent Work') will return urgent work

Upper('') - Convert all characters to uppercase characters.

select Upper('Urgent Work') will return URGENT WORK

LTrim('') - Removes spaces from given character strings on left.

select LTrim(' Urgent Work') will return Urgent Work

LTrim & RTRIM & Trim donot remove tab or line feed character.

RTrim('') - Removes space from given character strings on right.

select LTrim('Urgent Work ')  returns Urgent Work

Trim('') - Removes spaces from given character strings from both left and right.

select LTrim(' Urgent Work ')  returns Urgent Work

SubString('') - Returns a part of string from original string.

SubString(character_expression, position, length)

position - specifies where the substring begins.

length - specifies the length of the substring as number of characters.

Example: select SubString('Urgent Work',1,6)  return "Urgent"

Replace('') - Replace the desired string within the original string.

Replace(character_expression, searchstring, replacementstring)

SearchString - string which you want to replace.

ReplaceString - new string which you want to replace with

select replace('Think Big To Achieve Big', 'Big', 'Small')

Original - Think Big To Achieve Big

Result - Think SmallTo Achieve Small

Right('') - extract particular characters from right part of given string expression.

select right('Think High To Achieve High',15) will return "To Achieve High"

This function will be helpful when you want particular characters from right part.

Example: Let say i have social security nos. and i want to extract last 4 digit of it.

select right('111-11-1111',4) will return 1111

select right('222-22-2222',4) will return 2222

select right('333-33-3333',4) will return 3333

select right('444-44-4444',4) will return 4444