January 10, 2014 at 7:28 am
Hi there,
I need to split NUMERIC & ALPHABETICAL values from string.
for eg :-
1234heaven56-guy
output
123456 heaven-guy
Anyone help me out?
Thanks in advance
January 10, 2014 at 8:39 am
Here's one solution using a cteTally. You could turn this code into an inline table-valued function to use it against a query. If you have any questions on how it works, feel free to ask.
DECLARE @pString varchar(8000) = '1234heaven56-guy';
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
SELECT (SELECT SUBSTRING( @pString, N, 1)
FROM cteTally
WHERE SUBSTRING( @pString, N, 1) LIKE '[0-9]'
FOR XML PATH(''), TYPE).value('.','varchar(8000)') AS numbers,
(SELECT SUBSTRING( @pString, N, 1)
FROM cteTally
WHERE SUBSTRING( @pString, N, 1) LIKE '[^0-9]'
FOR XML PATH(''), TYPE).value('.','varchar(8000)') AS characters;
References:
Tally table: http://www.sqlservercentral.com/articles/T-SQL/62867/ & http://www.sqlservercentral.com/articles/Tally+Table/72993/
Inline table-valued function & how to use it: http://www.sqlservercentral.com/articles/T-SQL/91724/
January 12, 2014 at 5:47 pm
You can also use a pattern-based string splitter like PatternSplitCM, which can be found in the 4th article in my signature links.
WITH SampleData (MixedStr) AS
(
SELECT '1234heaven56-guy'
),
SplitByPattern AS
(
SELECT *
FROM SampleData a
CROSS APPLY dbo.PatternSplitCM(MixedStr, '[0-9]') b
)
SELECT MixedStr
,numerics=
(
SELECT Item + ''
FROM SplitByPattern
WHERE [Matched]=1
ORDER BY ItemNumber
FOR XML PATH('')
)
,others=
(
SELECT Item + ''
FROM SplitByPattern
WHERE [Matched]=0
ORDER BY ItemNumber
FOR XML PATH('')
)
FROM SplitByPattern a
GROUP BY MixedStr;
Beware though, that if MixedStr contains some of the XML-oriented characters, you'll need to use the more complex form of FOR XML PATH, like Wayne Sheffield describes in this article: Creating a comma-separated list[/url]
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
January 13, 2014 at 8:13 am
Are you using the right tool for the job?
I imagine the T-SQL solutions provided so far are clever (but they looked too complicated for me to casually understand & appreciate fully)
If this overloaded/composite string is coming from a client application, could you specify a parameter for each piece rather than concatenating everything into a single string?
If this value has already been stored in a table field, who wrote this schema? Yeah, I know... they didn't expect the database would have any reason to read data outside their application. If that's the case, why are you trying to read what might as well be treated as a custom object serialization (aka: none of your business)?
If this is a data import from a flat file, then I concede you might have little other option. I would still question how the flat file is being generated - can you request a CSV? Is this string actually fixed width values? (sometimes fixed width fields pack more densely than all the comma-overhead of CSV) In that case, you can would be able to parse the data by byte position for each row.
fwiw, I'm not trying to be difficult. When I run into problems like these, I step back and ask "is this the right tier for doing this work?" and I suspect in this case the answer is "probably not."
Could you give more background for how/why this problem exists?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply