A quick query puzzle:

  • adrian.facio (10/18/2012)


    Look, i came up with this, how do you like it?. I test the function in Winforms project but im pretty sure it will work nice in sql clr

    Imports System

    Imports System.Data

    Imports System.Data.SqlClient

    Imports System.Data.SqlTypes

    Imports Microsoft.SqlServer.Server

    Imports System.Text.RegularExpressions

    Partial Public Class UserDefinedFunctions

    <Microsoft.SqlServer.Server.SqlFunction()> _

    Public Shared Function ReplaceWithX(ByVal StringToReplace As SqlString) As SqlString

    ' Add your code here

    Dim expression As New Regex("[0-9]{6,}", RegexOptions.Compiled)

    'Dim evaluator As MatchEvaluator = AddressOf CharReplacement

    Dim Result As String = expression.Replace(StringToReplace.ToString, Function(m As Match) New String("x", m.Length))

    Return New SqlString(Result)

    End Function

    End Class

    As I've pointed out on this forum few times: If you want your CLR with RegEx to have the best performance, you should declare RegEx object as static (at class level), so it will not need to be initialised on every function call...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • This is the way you propose. Makes sense to initialize the object only once.

    Imports System

    Imports System.Data

    Imports System.Data.SqlClient

    Imports System.Data.SqlTypes

    Imports Microsoft.SqlServer.Server

    Imports System.Text.RegularExpressions

    Partial Public Class UserDefinedFunctions

    Private expression As New Regex("[0-9]{6,}", RegexOptions.Compiled)

    <Microsoft.SqlServer.Server.SqlFunction()> _

    Public Shared Function ReplaceWithX(ByVal StringToReplace As SqlString) As SqlString

    Dim Result As String = expression.Replace(StringToReplace.ToString, Function(m As Match) New String("x", m.Length))

    Return New SqlString(Result)

    End Function

    End Class

  • To all who participated in this thread:

    You may wish to review this new article:

    http://www.sqlservercentral.com/articles/String+Manipulation/94365/

    It provides a good utility function for solving this case and many other similar ones. Thanks to the OP for being the inspiration for the article!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (11/29/2012)


    To all who participated in this thread:

    You may wish to review this new article:

    http://www.sqlservercentral.com/articles/String+Manipulation/94365/

    It provides a good utility function for solving this case and many other similar ones. Thanks to the OP for being the inspiration for the article!

    Thanks Dwain

    I've already had a look at it and I am going to have a really good read through it soon. Even though I haven't had a chance to dig into the functionality and digest it properly I was impressed with the methods you came up with. Great article:-)

  • mickyT (11/29/2012)


    dwain.c (11/29/2012)


    To all who participated in this thread:

    You may wish to review this new article:

    http://www.sqlservercentral.com/articles/String+Manipulation/94365/

    It provides a good utility function for solving this case and many other similar ones. Thanks to the OP for being the inspiration for the article!

    Thanks Dwain

    I've already had a look at it and I am going to have a really good read through it soon. Even though I haven't had a chance to dig into the functionality and digest it properly I was impressed with the methods you came up with. Great article:-)

    Gosh Micky - Thanks for noticing and reading it and thanks for the praise! Hope you join in the discussion thread after all is said and done - and don't forget to rate the article!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Copying my article response here for anyone that doesn't get as far as reading all the comments:

    SSC author Solomon Rutzky[/url] has the free SQL# library available at http://www.sqlsharp.com. Among the many features available in the free version running under the SAFE permission set is the RegEx_Replace function. Taking the example from this thread:

    Given an input string such as: 1234ABC123456XYZ1234567890ADS, I would like to replace any string of digits that is longer than 5 characters with some other character (e.g., ‘x’), while leaving the remaining characters in the string unchanged.

    The whole solution is:

    SELECT

    SQL#.RegEx_Replace(N'1234ABC123456XYZ1234567890ADS', N'\d{5,}', N'x', -1, 1, NULL);

    Producing the result:

    1234ABCxXYZxADS

    There are all sorts of powerful and useful functions in this library, for example, if you want to list the matches for a regular expression (which is a much more powerful superset of the CHARINDEX and PATINDEX syntax), you can use:

    SELECT * FROM SQL#.RegEx_Matches(N'1234ABC123456XYZ1234567890ADS', N'\d{5,}', 1, NULL);

    +----------------------------------------------------+

    ¦ MatchNum ¦ Value ¦ StartPos ¦ EndPos ¦ Length ¦

    ¦----------+------------+----------+--------+--------¦

    ¦ 1 ¦ 123456 ¦ 8 ¦ 13 ¦ 6 ¦

    ¦ 2 ¦ 1234567890 ¦ 17 ¦ 26 ¦ 10 ¦

    +----------------------------------------------------+

  • Complete list of Simple and complex t-sql puzzles at

    http://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/

    This blog is useful for mid level developers , Strictly not for SQL Experts 🙂

    Enjoy...

    Regards,
    Pawan Kumar Khowal
    MSBISkills.com

  • pawankkmr 41145 (5/26/2015)


    Complete list of Simple and complex t-sql puzzles at

    http://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/

    This blog is useful for mid level developers , Strictly not for SQL Experts 🙂

    Enjoy...

    The problem with such puzzles are the restrictions. For example, the hierarchical problem restricts to SELECTs and CTEs, which is not a high performance solution in this case. They also seem to promote the idea that "set based" means "all in one query", which is patently not true and seems to get people into a lot of trouble performance wise.

    Still, fun/intellectual stuff. Thanks for posting the link.

    --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 (5/26/2015)


    pawankkmr 41145 (5/26/2015)


    Complete list of Simple and complex t-sql puzzles at

    http://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/

    This blog is useful for mid level developers , Strictly not for SQL Experts 🙂

    Enjoy...

    The problem with such puzzles are the restrictions. For example, the hierarchical problem restricts to SELECTs and CTEs, which is not a high performance solution in this case. They also seem to promote the idea that "set based" means "all in one query", which is patently not true and seems to get people into a lot of trouble performance wise.

    Still, fun/intellectual stuff. Thanks for posting the link.

    For the Interview questions on that site, more specifically some of the answers, typical frightening results in a lot of cases. For example, I'm one of the biggest advocates of using IDENTITY columns ever but even I know that a lot of the following isn't right and none of it is always true.

    Indexes

    6. Every should have a column called ID (Probably Identity) with INT data type and we should have Clustered Index on that column.

    I can see a front-ender with a little SQL Experience (very little) thinking such things but if a Senior Application Developer, Database Developer of any level, or DBA of any level said such a thing during an interview with me, I'd ask another question in a similar vein to see if I got the same kind of answer and, if I did, that would very likely be the end of the interview.

    You folks really need to sharpen up the Interview Q'n'A'.

    --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)

  • pawankkmr 41145 (5/26/2015)


    Complete list of Simple and complex t-sql puzzles at

    http://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/

    This blog is useful for mid level developers , Strictly not for SQL Experts 🙂

    Enjoy...

    Definitely some interesting puzzles in there. Clearly a lot of work went into pulling the post together.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Hi Jeff,

    Thank you very much ! Sure will try to improve on Q&As. Will update that post also.

    Regards,

    Pawan

    Regards,
    Pawan Kumar Khowal
    MSBISkills.com

  • Thanks dwain !

    Regards,

    Pawan

    MSBISkills.com

    Regards,
    Pawan Kumar Khowal
    MSBISkills.com

  • pawankkmr 41145 (5/26/2015)


    Hi Jeff,

    Thank you very much ! Sure will try to improve on Q&As. Will update that post also.

    Regards,

    Pawan

    Thank you for the feedback, Pawan.

    --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)

  • dwain.c (5/26/2015)


    pawankkmr 41145 (5/26/2015)


    Complete list of Simple and complex t-sql puzzles at

    http://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/

    This blog is useful for mid level developers , Strictly not for SQL Experts 🙂

    Enjoy...

    Definitely some interesting puzzles in there. Clearly a lot of work went into pulling the post together.

    Ditto that!

    --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)

  • One more method to replace 6 consecutive digits from the input string. 🙂

    --Create table and insert some data

    CREATE TABLE DigitsToReplace

    (

    Chrs VARCHAR(1000)

    )

    GO

    INSERT INTO DigitsToReplace VALUES ('1234ABC123456XYZ1234567890ADS')

    INSERT INTO DigitsToReplace VALUES ('1234')

    INSERT INTO DigitsToReplace VALUES ('123456')

    INSERT INTO DigitsToReplace VALUES ('123456A!@#$%')

    INSERT INTO DigitsToReplace VALUES ('')

    INSERT INTO DigitsToReplace VALUES (' ')

    INSERT INTO DigitsToReplace VALUES ('9876542345672a345673456734567')

    INSERT INTO DigitsToReplace VALUES ('9876542345672345673456734567')

    INSERT INTO DigitsToReplace VALUES ('a1AAAAAAAAAAAAAAAAAAAAAAA12345')

    INSERT INTO DigitsToReplace VALUES ('AAAAAA')

    --Create a Table Valued function

    CREATE FUNCTION [dbo].[Replace6ContinousDigits]

    (

    @InputString VARCHAR(1000)

    )

    RETURNS @results TABLE

    (

    final VARCHAR(1000)

    )

    AS

    BEGIN

    DECLARE @Sttrs AS VARCHAR(1000) = @InputString

    DECLARE @totals AS INT = 0

    DECLARE @Counter AS INT = 1

    SET @totals = DATALENGTH(@Sttrs)

    DECLARE @Chrs TABLE (rnk TinyInt , Chars Char(1) , Digit TinyInt)

    WHILE (@Counter <= @totals)

    BEGIN

    INSERT INTO @Chrs VALUES

    (

    @Counter

    ,SUBSTRING(@Sttrs,@Counter,1)

    ,CASE WHEN SUBSTRING(@Sttrs,@Counter,1) LIKE '[0-9]' THEN 1 ELSE @Counter END

    )

    SET @Counter = @Counter + 1

    END

    ;WITH CTE AS

    (

    SELECT *,

    CASE WHEN Digit = lag(Digit) OVER(ORDER BY rnk) THEN 0 ELSE 1 END cols

    FROM @Chrs c2

    )

    ,CTE1 AS

    (

    SELECT * , SUM(cols) OVER (ORDER BY rnk) grouper FROM CTE

    )

    ,CTE2 AS

    (

    SELECT *, COUNT(*) OVER (PARTITION BY grouper) ConsecutiveDigits FROM CTE1

    )

    ,CTE3 AS

    (

    SELECT chars, ConsecutiveDigits , ROW_NUMBER() OVER (PARTITION BY ConsecutiveDigits ORDER BY (SELECT NULL)) nkr

    FROM CTE2 c

    WHERE c.ConsecutiveDigits > 5

    )

    UPDATE c SET c.Chars = 'X'

    FROM CTE3 c

    DECLARE @final AS VARCHAR(1000) = ''

    SELECT @final = @final + Chars FROM @Chrs

    INSERT @results (final) SELECT @final

    RETURN;

    END

    ---Usage

    SELECT Chrs InputString, final ReplacedString FROM DigitsToReplace

    CROSS APPLY

    (

    SELECT final FROM [dbo].[Replace6ContinousDigits] (Chrs)

    ) A

    Pawan Kumar Khowal

    MSBISkills.com

    SQL Server Puzzles[/url]

    Regards,
    Pawan Kumar Khowal
    MSBISkills.com

Viewing 15 posts - 31 through 44 (of 44 total)

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