February 20, 2014 at 5:00 pm
I have a table ("MyData") with string columns that have nvarchar data that looks like this:
ColA
--------
42/90
78/109
I plan to do a mathematical grouping on the numerator (eg: 0-10,11-20,21-30, etc... ), So I need to grab the numerator and turn it into an int. For reasons I can't get into, I have to do this in pure T-SQL.
So my question: How would I write a select statement that regex pattern matches "^([0-9]+)/" on ColA and returns integers instead of text?
E.g. THis Query:
SELECT [magic t-sql syntax] as Converted_ColA from MyData
should return this set of int values:
42
78
Sincerely,
-e
February 20, 2014 at 5:10 pm
SELECT CONVERT( INT, LEFT( column, CHARINDEX( column, '\') - 1))?
What chapter in which textbook are you guys working on? That'll help us know what they're trying to get you to do. If it's not homework I'm not sure of the whole 'only in T-SQL' comment... but it's certainly easy enough to do in T-SQL or anywhere else.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 20, 2014 at 6:32 pm
Evil Kraig F (2/20/2014)
What chapter in which textbook are you guys working on? That'll help us know what they're trying to get you to do.
🙂
That brought a smile to my lips.
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
February 20, 2014 at 8:15 pm
Evil Kraig F (2/20/2014)
SELECT CONVERT( INT, LEFT( column, CHARINDEX( column, '\') - 1))?What chapter in which textbook are you guys working on? That'll help us know what they're trying to get you to do. If it's not homework I'm not sure of the whole 'only in T-SQL' comment... but it's certainly easy enough to do in T-SQL or anywhere else.
Thanks Evil Kraig! There are actually business reasons for the T-SQL requirement that I can't discuss on this forum. Your solution works for this specific requirement, but I truly need a regex solution because the format of the text is not known ahead of time. The customers will be providing regular experessions to match the part of the string they care about.
February 20, 2014 at 8:48 pm
ericjorg (2/20/2014)
... I truly need a regex solution because the format of the text is not known ahead of time. The customers will be providing regular experessions to match the part of the string they care about.
The above library of SQL functions (install is really smooth) can deal with RegEx.
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
February 20, 2014 at 9:00 pm
ericjorg (2/20/2014)
Thanks Evil Kraig! There are actually business reasons for the T-SQL requirement that I can't discuss on this forum. Your solution works for this specific requirement, but I truly need a regex solution because the format of the text is not known ahead of time. The customers will be providing regular experessions to match the part of the string they care about.
Oh gods... um... ow.
May I recommend CLR? T-SQL and regex are not friends. Honestly, even the best string splitter the community has coded up in T-SQL gets dwarfed by CLR (Find the thread for the DelimitedSplit8k, then some of Paul White's posts on it). However, that DOES break your 'only T-SQL' comment... which makes sense in hindsight after a bit of thought writing this little snippet down.
You can't really regex that way in T-SQL, particularly not for user supplies regexes. There's no actual regex that you can transfer to the system. For example, LIKE doesn't carry an 'anchor' character to force it to look at the beginning of a line. Without knowing the # of characters, [0-9] syntax won't work either, not directly.
If this absolutely has to be done via T-SQL, particularly for repeating characters of unknown length, you're going to have to code up a string splitter for per character, look for outlying conditions, then check the internals of each string for the repeating conditions. From there, you'll have to do that for each row they're searching.
I would shoot any developer who brought me such a solution, by the by. This shouldn't be done, there are other tools built to do exactly that which won't destroy performance on the server.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 20, 2014 at 9:07 pm
The SQL Sharp library is (I'm pretty sure) a CLR.
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
February 20, 2014 at 9:37 pm
Oh gods... um... ow.
May I recommend CLR? T-SQL and regex are not friends.
I thought as much, but I had to give it a try here before giving up. The current solution actually *is* a CLR method, but the customer is switching to a framework that re-issues queries and does not support CLR calls, so we are in between a rock and a hard place.
THanks for your help and input!
-e
February 21, 2014 at 9:20 am
ericjorg (2/20/2014)
Oh gods... um... ow.
May I recommend CLR? T-SQL and regex are not friends.
I thought as much, but I had to give it a try here before giving up. The current solution actually *is* a CLR method, but the customer is switching to a framework that re-issues queries and does not support CLR calls, so we are in between a rock and a hard place.
THanks for your help and input!
-e
Hi there. First off:
Second, what exactly does "re-issues queries" really mean? Are those being cached along with the results and somehow not hitting SQL Server on the "re-issue"? Anything that hits SQL Server should not really care about (or even know about) built-in vs SQLCLR vs extended stored procedure. Although I suppose Windows Azure SQL Database (formerly SQL Azure) doesn't support SQLCLR.
Does this also mean that the SQL is being generated by the app as opposed to calling a Stored Procedure?
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply