Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


string extraction help


string extraction help

Author
Message
olibbhq
olibbhq
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 351
Hi,

I have some code in c# which extracts the text from a string that complies with these wildcards

([A-Za-z]{3,6}_?[0-9]{4,7})

to break this down
first 3 to 6 characters needs to be upper or lower case letters
then there could be an underscore
then the next 4 to 7 characters have to be numbers

Does anyone know how to create a sql equivalent. Should I use PATINDEX, if so how to I denote the max and min length?

Many Thanks for your help, it is very much appreciated,

Oliver
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16667 Visits: 17030
Can you use CLR? RegEx tends to be far easier to implement in C# than in pure t-sql. There are certainly some pattern matching techniques for some basics but I am unaware of more complicated matching like you have going on.

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
olibbhq
olibbhq
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 351
Thanks for this, my CLR skills are non - existent but it is something I need to look into. I don't think such powerful query tools exist to do this in SQL, I guess.

Many Thanks for the reply.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8409 Visits: 19510
oliver.morris (1/14/2013)
Thanks for this, my CLR skills are non - existent but it is something I need to look into. I don't think such powerful query tools exist to do this in SQL, I guess.

Many Thanks for the reply.


No they don't - this requirement is definitely a strong CLR candidate.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8409 Visits: 19510
Or if you can use SSIS to achieve whatever it is that you are doing, there is at least one CodePlex add-on that would allow you to use RegEx matching without having to write code ...


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
olibbhq
olibbhq
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 351
Many Thanks for your help, this is good to know. Will try the CLR route.

Cheers
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4287 Visits: 6431
It might seem a bit tricky and you may need to play with this a bit to get it working 100%, but I think you'll get the idea of what I'm trying to do here pretty quickly.


WITH SampleData (s) AS (
-- RegEx: ([A-Za-z]{3,6}_?[0-9]{4,7})
SELECT 'ABVAAA_1234567' -- valid
UNION ALL SELECT 'AASDAAA_123' -- invalid
UNION ALL SELECT 'ACd123' -- valid
UNION ALL SELECT 'ACD_12345677' -- invalid
UNION ALL SELECT '12345677' -- invalid
)
SELECT s, PosUnd, PosNum, PosAlph, LEN(s)
,IsValid=CASE
WHEN PosUnd > 0 AND PosAlph <> 0 AND PosNum - PosUnd = 1 AND PosUnd - PosAlph <= 6 AND LEN(s) - PosNum <= 6
THEN 1
WHEN PosUnd = 0 AND PosAlph <> 0 AND PosUnd - PosAlph <= 7 AND LEN(s) - PosNum <= 7
THEN 1
--WHEN
ELSE 0 END
FROM SampleData
CROSS APPLY (SELECT PATINDEX('%[_]%', s)) a(PosUnd)
CROSS APPLY (SELECT PATINDEX('%[0-9]%', s)) b(PosNum)
CROSS APPLY (SELECT PATINDEX('%[A-Za-z]%', s)) c(PosAlph)




Alternatively, using CLRs and understanding how to write them don't need to go hand in hand. You just need to have permissions on your database to install them.

Here's a pretty nice library called SQL# developed by Solomon Rutzky (SQL Sharp Library of CLRs) that is really clean to install, is well documented and has the functions in it that you'll need to perform this validity check directly with the RegEx pattern you've provided. RegEx validations will probably be more costly in terms of CPU than doing something like what I provided, but you can always test that assumption (and you should) to be sure.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
olibbhq
olibbhq
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 351
I see what you are up to here. This is a really neat method and will work it up. Thank you for sending over the CLR reference, I will check this out as well.

I really appreciate your help,

Oliver
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4287 Visits: 6431
oliver.morris (1/15/2013)
I see what you are up to here. This is a really neat method and will work it up. Thank you for sending over the CLR reference, I will check this out as well.

I really appreciate your help,

Oliver


Happy to be of service! Cool


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search