Showing posts with label string manipulation. Show all posts
Showing posts with label string manipulation. Show all posts

Tuesday, January 13, 2009

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

Finding the Last Occurrence of a Pattern in a String

When dealing with string data, one of the most useful Transact-SQL functions you can have in your toolbox is PATINDEX. Aside from it’s many creative uses (and there are quite a few), it allows you to search for an expression (including wildcard operators) within any valid SQL Server character type. It returns the position of the first occurrence of the pattern within the string, and returns zero (0) if the pattern is not found. As always, an example is worth 1000 bon mots:

SELECT PATINDEX('%c%''abcdefgabcdefga')

The above snippet returns 3, because the first instance of “c” within “abcdefgabcdefga” is at the third character.

On occasion, you may be required to find the last occurrence of the pattern rather than the first. This usually happens when you’re dealing with data from external systems, or when you’ve inherited custody of an inappropriately denormalized system (one where more than one “logical” data item has been stored in a single, concatenated field). I have the same requirement for my “mad science” series, so I developed a function to take care of it. Here it is:

CREATE FUNCTION dbo.REVERSEPATINDEX(@StringToFind    nvarchar(max),

@StringToSearch nvarchar(max))

RETURNS bigint

AS

BEGIN

--LastIndex will store the "previous" index of the pattern

--while we search for the "next" index inside our string.

DECLARE @LastIndex bigint, @NextIndex bigint


--We will chop off the "searched" portion of our string

--After each search iteration

DECLARE @CurrentStringToSearch nvarchar(max)


--Assign the value of the input parameter to @CurrentStringToSearch

SET @CurrentStringToSearch = @StringToSearch


--Grab the first occurrence, if it exists, using PATINDEX

SET @NextIndex = PATINDEX(@StringToFind, @CurrentStringToSearch)


--If we've found the pattern within the string, continue and find

--subsequent occurrences until there are no more to find.

WHILE @NextIndex > 0

BEGIN

--Add @NextIndex to @LastIndex, effectively storing within

--@LastIndex the index of the occurence that we have just found,

--based upon the original (non-truncated) string.

SET @LastIndex = COALESCE(@LastIndex,0) + @NextIndex


--Truncate @CurrentStringToSearch, removing the portion of the

--string that we have already searched.

SET @CurrentStringToSearch
= RIGHT(@CurrentStringToSearch, LEN(@CurrentStringToSearch) - @NextIndex)


--Find the NEXT occurence of the pattern in the remaining portion of

--the string to search.

SET @NextIndex = PATINDEX(@StringToFind, @CurrentStringToSearch)

END


--We will COALESCE @LastIndex and @NextIndex; @LastIndex will be NULL

--if the pattern does not exist in the string (as the loop above will

--never have been executed).

RETURN COALESCE(@LastIndex, @NextIndex)

END

GO

And to test:

SELECT dbo.REVERSEPATINDEX('%c%', 'abcdefgabcdefga')

Returns 10 – the correct answer, given that the last occurrence of “c” in “abcdefgabcdefga” is at the tenth character.

Wednesday, December 3, 2008

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

Friday, November 7, 2008

String manipulations

        Dim strSample As String = "Left Center Right"

        'left
        strSample = strSample.Substring(0, 4)

        'center
        strSample = strSample.Substring(5, 6)

        'right
        strSample = strSample.Substring(12)


        'split
        Dim SplitString As String = "Cat, Dog, Hen, Zebra"
        Dim SplitArr() As String
        Dim i As Integer

        SplitArr = SplitString.Split(","c)

        For i = 0 To SplitArr.Length - 1
            console.writeline(SplitArr(i))
        Next

        'Join
        Dim JoinArr(2) As String
        Dim JoinString As String
        JoinArr(0) = "Give"
        JoinArr(1) = "Take"
        JoinArr(2) = "Apply"
        JoinString = String.Join(",", JoinArr)
        Console.WriteLine(JoinString)

        'insert
        Dim strTemp As String = "This is My Stng"
        strSample = strTemp.Insert(13, "ri")
        Console.WriteLine(strSample)    'OUTPUT: "This is My String".

        'Remove
        Dim RemoveString As String = "This is my Sam@o@o@ple"
        'strSample = "This is my Sample"
        strSample = RemoveString.Remove(14, 5)
        Console.WriteLine(strSample)

        'replace
        Dim ReplaceString = "This is My String"
        ' strSample = "This is Another String"
        strSample = ReplaceString.Replace("My", "Another")
        Console.WriteLine(strSample)

        'Trim Spaces
        Dim SpaceString As String = "        This string will have the spaces removed        "
        Console.WriteLine(SpaceString)
        Dim TrimmedString As String
        TrimmedString = Trim(SpaceString)
        Console.WriteLine(TrimmedString)

        'Trim Other
        Dim HashString As String = "#####Testing!######"
        TrimmedString = HashString.Trim("#")
        Console.WriteLine(TrimmedString)

        'Left Trim ( TrimStart)
        Dim SpaceString1 As String = "        This string will have the leading spaces removed        "
        Console.WriteLine(SpaceString)
        TrimmedString = SpaceString1.TrimStart(" ")
        Console.WriteLine(TrimmedString)

        'Right Trim ( TrimEnd)
        Dim SpaceString2 As String = "        This string will have the trailing spaces removed        "
        Console.WriteLine(SpaceString)
        TrimmedString = SpaceString.TrimEnd(" ")
        Console.WriteLine(TrimmedString)

        'Pad Left
        Dim MainString As String = "This will be padded on the left"
        Dim PaddedString As String

        PaddedString = MainString.PadLeft(36, "@") '36 = new length of string with 5 new chars
        Console.WriteLine(PaddedString)

        'Pad Right
        Dim MainString1 As String = "This will be padded on the right"
        PaddedString = MainString1.PadRight(36, "@") '36 = new length of string with 5 new chars
        Console.WriteLine(PaddedString)

        'UPPER CASE
        Dim MixedString As String = "UpPeR oR LoWeR cAsE"
        Dim UPPERString As String
        ' UPPERString = "UPPER OR LOWER CASE"
        UPPERString = MixedString.ToUpper
        Console.WriteLine(UPPERString)

        'lower case
        Dim MixedString1 As String = "UpPeR oR LoWeR cAsE"
        Dim lowerString As String
        'lowerString = "upper or lower case"
        lowerString = MixedString1.ToLower
        Console.WriteLine(lowerString)

        'TitleCase
        Dim TitleString As String = "UPPER lower Title"
        TitleString = TitleString.ToLower()  ' mainly because of UPPER not changing to title
        TitleString = System.Globalization.CultureInfo.InstalledUICulture.TextInfo.ToTitleCase(TitleString)
        Console.WriteLine(TitleString)

        'Format
        Dim UnFormattedDateTime As Date = #1/27/2001 5:04:23 PM#
        Dim strFormattedString As String
        ' Returns current system time in the system-defined long time format.
        strFormattedString = Format(Now(), "Long Time")
        ' Returns current system date in the system-defined long date format.
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(Now(), "Long Date")
        Console.WriteLine(strFormattedString)
        ' Also returns current system date in the system-defined long date 
        ' format, using the single letter code for the format.
        strFormattedString = Format(Now(), "D")
        Console.WriteLine(strFormattedString)
        ' Returns the value of UnFormattedDateTime in user-defined date/time formats.
        strFormattedString = Format(UnFormattedDateTime, "h:m:s")   ' Returns "5:4:23".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(UnFormattedDateTime, "hh:mm:ss tt")   ' Returns "05:04:23 PM".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(UnFormattedDateTime, "dddd, MMM d yyyy")   ' Returns "Saturday,
        ' Jan 27 2001".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(UnFormattedDateTime, "HH:mm:ss")   ' Returns "17:04:23"
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(23)   ' Returns "23".
        Console.WriteLine(strFormattedString)
        ' User-defined numeric formats.
        strFormattedString = Format(5459.4, "##,##0.00")   ' Returns "5,459.40".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(334.9, "###0.00")   ' Returns "334.90".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(5, "0.00%")   ' Returns "500.00%".
        Console.WriteLine(strFormattedString)

        'Concat
        Dim ConcatString As String
        Dim aString As String = "A"
        Dim bString As String = "B"
        Dim cString As String = "C"
        Dim dString As String = "D"
        ' ConcatString = "ABCD"
        ConcatString = String.Concat(aString, bString, cString, dString)
        Console.WriteLine(ConcatString)

        'IndexOf
        Dim MainString2 As String = "ABCDE"
        Dim Result As Integer
        Result = MainString2.IndexOf("D")  ' result = 3
        Console.WriteLine("D is Character number : " & Result.ToString)

        'Chars
        Dim MainString3 As String = "ABCDE"
        Dim ResultChar As Char
        ResultChar = MainString3.Chars(3) ' resultChar = "D"
        Console.WriteLine("Character 3 is : " & ResultChar.ToString)

        'LastIndexOf
        Dim MainString4 = "A\B\C\D\E\F\G\H\I\J"
        Result = MainString.LastIndexOf("\")
        Console.WriteLine("Last occurence of ' \ ' is at Position : " & Result.ToString())

        'Compare
        Dim FirstString As String = "Alphabetical"
        Dim SecondString As String = "Order"
        Dim ThirdString As String = "Order"
        Dim FourthString As String = "Alphabetical"
        Dim Result1 As Integer
        Dim Result2 As Integer
        Result1 = String.Compare(FirstString, SecondString)
        Console.WriteLine("Result = " & Result1.ToString & Environment.NewLine & "First String Is Smaller Than Second")
        Result2 = String.Compare(ThirdString, FourthString)
        Console.WriteLine("Result = " & Result2.ToString & Environment.NewLine & "Third String Is Greater Than Fourth")

        'Length
        Dim MainString5 As String = "String To Test The Length"
        Dim Result3 As Integer = MainString.Length
        Console.WriteLine(Result.ToString & " Characters")

        'Copy
        Dim EmptyString As String
        Dim CopyString As String = "This Will Be Copied Into An Empty String"
        EmptyString = String.Copy(CopyString)
        Console.WriteLine(EmptyString)

String manipulations

        Dim strSample As String = "Left Center Right"

        'left
        strSample = strSample.Substring(0, 4)

        'center
        strSample = strSample.Substring(5, 6)

        'right
        strSample = strSample.Substring(12)


        'split
        Dim SplitString As String = "Cat, Dog, Hen, Zebra"
        Dim SplitArr() As String
        Dim i As Integer

        SplitArr = SplitString.Split(","c)

        For i = 0 To SplitArr.Length - 1
            console.writeline(SplitArr(i))
        Next

        'Join
        Dim JoinArr(2) As String
        Dim JoinString As String
        JoinArr(0) = "Give"
        JoinArr(1) = "Take"
        JoinArr(2) = "Apply"
        JoinString = String.Join(",", JoinArr)
        Console.WriteLine(JoinString)

        'insert
        Dim strTemp As String = "This is My Stng"
        strSample = strTemp.Insert(13, "ri")
        Console.WriteLine(strSample)    'OUTPUT: "This is My String".

        'Remove
        Dim RemoveString As String = "This is my Sam@o@o@ple"
        'strSample = "This is my Sample"
        strSample = RemoveString.Remove(14, 5)
        Console.WriteLine(strSample)

        'replace
        Dim ReplaceString = "This is My String"
        ' strSample = "This is Another String"
        strSample = ReplaceString.Replace("My", "Another")
        Console.WriteLine(strSample)

        'Trim Spaces
        Dim SpaceString As String = "        This string will have the spaces removed        "
        Console.WriteLine(SpaceString)
        Dim TrimmedString As String
        TrimmedString = Trim(SpaceString)
        Console.WriteLine(TrimmedString)

        'Trim Other
        Dim HashString As String = "#####Testing!######"
        TrimmedString = HashString.Trim("#")
        Console.WriteLine(TrimmedString)

        'Left Trim ( TrimStart)
        Dim SpaceString1 As String = "        This string will have the leading spaces removed        "
        Console.WriteLine(SpaceString)
        TrimmedString = SpaceString1.TrimStart(" ")
        Console.WriteLine(TrimmedString)

        'Right Trim ( TrimEnd)
        Dim SpaceString2 As String = "        This string will have the trailing spaces removed        "
        Console.WriteLine(SpaceString)
        TrimmedString = SpaceString.TrimEnd(" ")
        Console.WriteLine(TrimmedString)

        'Pad Left
        Dim MainString As String = "This will be padded on the left"
        Dim PaddedString As String

        PaddedString = MainString.PadLeft(36, "@") '36 = new length of string with 5 new chars
        Console.WriteLine(PaddedString)

        'Pad Right
        Dim MainString1 As String = "This will be padded on the right"
        PaddedString = MainString1.PadRight(36, "@") '36 = new length of string with 5 new chars
        Console.WriteLine(PaddedString)

        'UPPER CASE
        Dim MixedString As String = "UpPeR oR LoWeR cAsE"
        Dim UPPERString As String
        ' UPPERString = "UPPER OR LOWER CASE"
        UPPERString = MixedString.ToUpper
        Console.WriteLine(UPPERString)

        'lower case
        Dim MixedString1 As String = "UpPeR oR LoWeR cAsE"
        Dim lowerString As String
        'lowerString = "upper or lower case"
        lowerString = MixedString1.ToLower
        Console.WriteLine(lowerString)

        'TitleCase
        Dim TitleString As String = "UPPER lower Title"
        TitleString = TitleString.ToLower()  ' mainly because of UPPER not changing to title
        TitleString = System.Globalization.CultureInfo.InstalledUICulture.TextInfo.ToTitleCase(TitleString)
        Console.WriteLine(TitleString)

        'Format
        Dim UnFormattedDateTime As Date = #1/27/2001 5:04:23 PM#
        Dim strFormattedString As String
        ' Returns current system time in the system-defined long time format.
        strFormattedString = Format(Now(), "Long Time")
        ' Returns current system date in the system-defined long date format.
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(Now(), "Long Date")
        Console.WriteLine(strFormattedString)
        ' Also returns current system date in the system-defined long date 
        ' format, using the single letter code for the format.
        strFormattedString = Format(Now(), "D")
        Console.WriteLine(strFormattedString)
        ' Returns the value of UnFormattedDateTime in user-defined date/time formats.
        strFormattedString = Format(UnFormattedDateTime, "h:m:s")   ' Returns "5:4:23".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(UnFormattedDateTime, "hh:mm:ss tt")   ' Returns "05:04:23 PM".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(UnFormattedDateTime, "dddd, MMM d yyyy")   ' Returns "Saturday,
        ' Jan 27 2001".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(UnFormattedDateTime, "HH:mm:ss")   ' Returns "17:04:23"
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(23)   ' Returns "23".
        Console.WriteLine(strFormattedString)
        ' User-defined numeric formats.
        strFormattedString = Format(5459.4, "##,##0.00")   ' Returns "5,459.40".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(334.9, "###0.00")   ' Returns "334.90".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(5, "0.00%")   ' Returns "500.00%".
        Console.WriteLine(strFormattedString)

        'Concat
        Dim ConcatString As String
        Dim aString As String = "A"
        Dim bString As String = "B"
        Dim cString As String = "C"
        Dim dString As String = "D"
        ' ConcatString = "ABCD"
        ConcatString = String.Concat(aString, bString, cString, dString)
        Console.WriteLine(ConcatString)

        'IndexOf
        Dim MainString2 As String = "ABCDE"
        Dim Result As Integer
        Result = MainString2.IndexOf("D")  ' result = 3
        Console.WriteLine("D is Character number : " & Result.ToString)

        'Chars
        Dim MainString3 As String = "ABCDE"
        Dim ResultChar As Char
        ResultChar = MainString3.Chars(3) ' resultChar = "D"
        Console.WriteLine("Character 3 is : " & ResultChar.ToString)

        'LastIndexOf
        Dim MainString4 = "A\B\C\D\E\F\G\H\I\J"
        Result = MainString.LastIndexOf("\")
        Console.WriteLine("Last occurence of ' \ ' is at Position : " & Result.ToString())

        'Compare
        Dim FirstString As String = "Alphabetical"
        Dim SecondString As String = "Order"
        Dim ThirdString As String = "Order"
        Dim FourthString As String = "Alphabetical"
        Dim Result1 As Integer
        Dim Result2 As Integer
        Result1 = String.Compare(FirstString, SecondString)
        Console.WriteLine("Result = " & Result1.ToString & Environment.NewLine & "First String Is Smaller Than Second")
        Result2 = String.Compare(ThirdString, FourthString)
        Console.WriteLine("Result = " & Result2.ToString & Environment.NewLine & "Third String Is Greater Than Fourth")

        'Length
        Dim MainString5 As String = "String To Test The Length"
        Dim Result3 As Integer = MainString.Length
        Console.WriteLine(Result.ToString & " Characters")

        'Copy
        Dim EmptyString As String
        Dim CopyString As String = "This Will Be Copied Into An Empty String"
        EmptyString = String.Copy(CopyString)
        Console.WriteLine(EmptyString)