October 19, 2012 at 3:41 am
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...
October 19, 2012 at 8:50 am
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
November 29, 2012 at 10:10 pm
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 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
November 29, 2012 at 10:52 pm
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:-)
November 29, 2012 at 11:31 pm
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 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
November 29, 2012 at 11:50 pm
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 ¦
+----------------------------------------------------+
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 26, 2015 at 9:33 am
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
May 26, 2015 at 9:51 am
pawankkmr 41145 (5/26/2015)
Complete list of Simple and complex t-sql puzzles athttp://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
Change is inevitable... Change for the better is not.
May 26, 2015 at 6:07 pm
Jeff Moden (5/26/2015)
pawankkmr 41145 (5/26/2015)
Complete list of Simple and complex t-sql puzzles athttp://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
Change is inevitable... Change for the better is not.
May 26, 2015 at 6:10 pm
pawankkmr 41145 (5/26/2015)
Complete list of Simple and complex t-sql puzzles athttp://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 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
May 26, 2015 at 8:22 pm
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
May 26, 2015 at 8:28 pm
Thanks dwain !
Regards,
Pawan
MSBISkills.com
Regards,
Pawan Kumar Khowal
MSBISkills.com
May 26, 2015 at 9:39 pm
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
Change is inevitable... Change for the better is not.
May 26, 2015 at 9:41 pm
dwain.c (5/26/2015)
pawankkmr 41145 (5/26/2015)
Complete list of Simple and complex t-sql puzzles athttp://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
Change is inevitable... Change for the better is not.
October 4, 2015 at 8:15 pm
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
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