uses of string splitters

  • Yes, I know about what you're talking about. I was lucky to once have a great manager tell me what an awesome job I did after I banged my head against a problem and gave him something he could use to make daily improvements. When he did that I thought, 'I am so awesome' (I did) and then I also told myself that it was child's play for the likes of Jeff Moden, Luis, Sean Lange....:-) But, the most important thing is you solved the problem the right way with the proper motivations and that's a story that repeated often enough eventually makes you like an expert and human being.

    Suprrrr! Yup, whether or not you ever post those scripts you can learn from them. ps. your friend just takes it for granted that you are awesome. She probably never thought otherwise and it would be too obvious to say.

  • KoldCoffee (5/30/2014)


    Yes, I know about what you're talking about. I was lucky to once have a great manager tell me what an awesome job I did after I banged my head against a problem and gave him something he could use to make daily improvements. When he did that I thought, 'I am so awesome' (I did) and then I also told myself that it was child's play for the likes of Jeff Moden, Luis, Sean Lange....:-) But, the most important thing is you solved the problem the right way with the proper motivations and that's a story that repeated often enough eventually makes you like an expert and human being.

    Suprrrr! Yup, whether or not you ever post those scripts you can learn from them. ps. your friend just takes it for granted that you are awesome. She probably never thought otherwise and it would be too obvious to say.

    Not sure I deserved to be grouped with those other two but thanks! Trust me there was a time I would have banged my head against the wall for something like that myself. In fact it wasn't all that long ago. It just takes practices and the willingness to share your ideas and learn from others.

    I too would like to see what you came up with if you find them.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (5/30/2014)


    KoldCoffee (5/30/2014)


    Yes, I know about what you're talking about. I was lucky to once have a great manager tell me what an awesome job I did after I banged my head against a problem and gave him something he could use to make daily improvements. When he did that I thought, 'I am so awesome' (I did) and then I also told myself that it was child's play for the likes of Jeff Moden, Luis, Sean Lange....:-) But, the most important thing is you solved the problem the right way with the proper motivations and that's a story that repeated often enough eventually makes you like an expert and human being.

    Suprrrr! Yup, whether or not you ever post those scripts you can learn from them. ps. your friend just takes it for granted that you are awesome. She probably never thought otherwise and it would be too obvious to say.

    Not sure I deserved to be grouped with those other two but thanks!

    That was exactly what I was thinking. 🙂

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I also have the Proper Case thread saved in my briefcase because it's good. Luis, great approach. BTW, I still have your Start With...Connect By solution in my briefcase too. 😉

    I've used the splitter for many different things, including passing an unknown number of IDs from a client application as a string to a procedure. Once there, simply split them by the delimiter and they're in a usable table format. A neat side affect is that you don't have to use dynamic SQL in the procedure, so you injection-proof the procedure. Other uses include extracting a filename from a table of full paths and filenames and of course splitting up incoming data.

    As for the tally table, I've heard it described as the "Swiss Army Knife of SQL" before. While it won't solve all the world's problems, it sure does a good job at what it's designed to do. It just a simple table of numbers, yet it has so many uses and it's screaming fast. You've probably already seen Jeff's original article, but in case you haven't, it is at http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  • Ed Wagner (5/30/2014)


    I also have the Proper Case thread saved in my briefcase because it's good. Luis, great approach. BTW, I still have your Start With...Connect By solution in my briefcase too. 😉

    I feel really honored :blush:

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I went to Tally Table site http://www.sqlservercentral.com/articles/T-SQL/62867/ folks here recommended. Would love clarification on how a loop for extracting the strings between a delimiter works, using his example.

    --=============================================================================

    -- Setup

    --=============================================================================

    USE TempDB --DB that everyone has where we can cause no harm

    SET NOCOUNT ON --Supress the auto-display of rowcounts for appearance/speed

    DECLARE @StartTime DATETIME --Timer to measure total duration

    SET @StartTime = GETDATE() --Start the timer

    --=============================================================================

    -- Create and populate a Tally table

    --=============================================================================

    --===== Conditionally drop and create the table/Primary Key

    IF OBJECT_ID('dbo.Tally') IS NOT NULL

    DROP TABLE dbo.Tally

    CREATE TABLE dbo.Tally

    (N INT,

    CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N))

    --===== Create and preset a loop counter

    DECLARE @Counter INT

    SET @Counter = 1

    --===== Populate the table using the loop and couner

    WHILE @Counter <= 11000

    BEGIN

    INSERT INTO dbo.Tally

    (N)

    VALUES (@Counter)

    SET @Counter = @Counter + 1

    END

    --===== Simulate a passed parameter

    DECLARE @Parameter VARCHAR(8000)

    SET @Parameter = 'Element01,Element02,Element03,Element04,Element05'

    --===== Create a table to store the results in

    DECLARE @Elements TABLE

    (

    Number INT IDENTITY(1,1), --Order it appears in original string

    Value VARCHAR(8000) --The string value of the element

    )

    --===== Add start and end commas to the Parameter so we can handle

    -- single elements

    SET @Parameter = ','+@Parameter +','

    --===== Join the Tally table to the string at the character level and

    -- when we find a comma, insert what's between that command and

    -- the next comma into the Elements table

    INSERT INTO @Elements

    (Value)

    SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)

    FROM dbo.Tally

    WHERE N < LEN(@Parameter)

    AND SUBSTRING(@Parameter,N,1) = ',' --Notice how we find the comma

    SELECT * FROM @Elements

    I don't fully get how this part of the INSERT statement works:

    SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)

    If N=1, then N+1=2 on each subsequent loop, I think the string being passed to this statement as @Parameter is getting truncated by the amount lobbed off in the prior pass.

    Am I right to understand that with each loop the value of @Parameter passed get shorter by one delimiter and the string between the first set of delimiters? As in:

    1rst time through: 'Element01,Element02,Element03,Element04,Element05'

    2nd time through: ,Element02,Element03,Element04,Element05'

    3rd time through: ,Element03,Element04,Element05'

    and so on?

    Is this what Jeff Moden means when he says "Again, all this does is find a comma and "remembers" its position. Then it uses CharIndex to find the next comma and inserts what's between the commas into a table variable. It quits looping when it runs out of commas. "

  • Seriously? No one could answer that question?

  • KoldCoffee (6/2/2014)


    Seriously? No one could answer that question?

    Heh... Seriously? You couldn't answer that question? 😉 Keep it light, KC.

    Look at the code again. You've taken the SUBSTRING out of context. What are the actual values of N according to the WHERE clause?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • OK I'll take a look.

    (on 'seriously', it was said with a wounded tone).:crying:

  • KoldCoffee (6/3/2014)


    OK I'll take a look.

    (on 'seriously', it was said with a wounded tone).:crying:

    Ah... got it.

    If you look at the WHERE clause, "N" will only be those values that represent the character positions from left to right of where the delimiters occur.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Luis Cazares (5/29/2014)


    Something like this?

    CREATE FUNCTION [dbo].[ProperCaseWithNumbers]

    (

    @StrIn VARCHAR(255)

    )

    RETURNS TABLE WITH SCHEMABINDING

    AS

    RETURN

    SELECT STUFF(

    ----Start of first parameter

    (SELECT CASE

    WHEN split.Item LIKE '%[0-9]%'

    THEN ' ' + split.Item

    ELSE ' ' + Upper(LEFT(split.Item, 1)) + Lower(Substring(split.Item, 2, 255))

    END AS word

    FROM dbo.Delimitedsplit8k(@StrIn, ' ') split

    FOR XML PATH('')

    ,TYPE

    ).value('.', 'varchar(255)') ----End of first parameter. List generated as: ',item1,item2,item3,...,itemN'

    , 1 ----Position (first character)

    , 1 ----Number of characters to be replaced (one, the first comma)

    , '' ----Replacement string (empty)

    ) AS ProperCased

    Oh man, I wish I saw something like this a year or so ago. I spent a heckuva long time trying to figure out where to put .value('.', 'nvarchar(MAX)') to make a variable stop printing weird &OXF or something when it hit char(10) and char(13). Thanks Luis.

    An example of string splitting that has come up fairly often for me has been folder paths stored in tables that need to either be trimmed to the nth '\' or the value between the nth occurrences needs to be pulled out. It gets fun when people start mixing UNC '\\' paths with system 'C:\' paths.

    I didn't see this code in the initial capitalization thread. It's what I've been using happily for a couple years. Not sure how it compares to the other solutions.

    CREATE FUNCTION dbo.InitialCap(@String VARCHAR(8000))

    /***************************************************************************************************

    Purpose:

    Capitalize any lower case alpha character which follows any non alpha character or single quote.

    Revision History:

    Rev 00 - 24 Feb 2010 - George Mastros - Initial concept

    http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/sql-server-proper-case-function

    Rev 01 - 25 Sep 2010 - Jeff Moden

    - Redaction for personal use and added documentation.

    - Slight speed enhancement by adding additional COLLATE clauses that shouldn't have mattered

    - and the reduction of multiple SET statements to just 2 SELECT statements.

    - Add no-cap single-quote by single-quote to the filter.

    ***************************************************************************************************/

    RETURNS VARCHAR(8000)

    AS

    BEGIN

    ----------------------------------------------------------------------------------------------------

    DECLARE @Position INT

    ;

    --===== Update the first character no matter what and then find the next postion that we

    -- need to update. The collation here is essential to making this so simple.

    -- A-z is equivalent to the slower A-Z

    SELECT @String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,

    @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)

    ;

    --===== Do the same thing over and over until we run out of places to capitalize.

    -- Note the reason for the speed here is that ONLY places that need capitalization

    -- are even considered for @Position using the speed of PATINDEX.

    WHILE @Position > 0

    SELECT @String = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,

    @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)

    ;

    ----------------------------------------------------------------------------------------------------

    RETURN @String;

    END ;

  • sqldriver (6/4/2014)


    I didn't see this code in the initial capitalization thread. It's what I've been using happily for a couple years. Not sure how it compares to the other solutions.

    You can test it, but I guess it will be slower as this is a scalar function with a while loop in it.

    The ProperCase Function thread has code to test the functions or you can test with your own information.

    Try to modify the functions to be able to handle exactly what you need.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (6/4/2014)


    sqldriver (6/4/2014)


    I didn't see this code in the initial capitalization thread. It's what I've been using happily for a couple years. Not sure how it compares to the other solutions.

    You can test it, but I guess it will be slower as this is a scalar function with a while loop in it.

    The ProperCase Function thread has code to test the functions or you can test with your own information.

    Try to modify the functions to be able to handle exactly what you need.

    Heh... I'm in a hell of a spot here... DelimitedSplit8K vs a very fast scalar function that I modified to be quicker. 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (6/4/2014)


    Luis Cazares (6/4/2014)


    sqldriver (6/4/2014)


    I didn't see this code in the initial capitalization thread. It's what I've been using happily for a couple years. Not sure how it compares to the other solutions.

    You can test it, but I guess it will be slower as this is a scalar function with a while loop in it.

    The ProperCase Function thread has code to test the functions or you can test with your own information.

    Try to modify the functions to be able to handle exactly what you need.

    Heh... I'm in a hell of a spot here... DelimitedSplit8K vs a very fast scalar function that I modified to be quicker. 😛

    In the (almost) words of Paul Weller - "this is the Moden world."

  • Jeff Moden (6/4/2014)


    Luis Cazares (6/4/2014)


    sqldriver (6/4/2014)


    I didn't see this code in the initial capitalization thread. It's what I've been using happily for a couple years. Not sure how it compares to the other solutions.

    You can test it, but I guess it will be slower as this is a scalar function with a while loop in it.

    The ProperCase Function thread has code to test the functions or you can test with your own information.

    Try to modify the functions to be able to handle exactly what you need.

    Heh... I'm in a hell of a spot here... DelimitedSplit8K vs a very fast scalar function that I modified to be quicker. 😛

    I'm stunned. After some tests, it seems than in most cases the scalar function will perform better and it seems to scale a lot better. The cases where it won't perform better are cases where the duration on 1000 rows goes under 0.1 second so it shouldn't be a big deal.

    Conclusion: Never say never.:-D

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 15 posts - 46 through 59 (of 59 total)

You must be logged in to reply to this topic. Login to reply