August 22, 2012 at 6:56 am
Hi,
I have to peform a string manipulation as below:
String manipulation shoud effect only the numbers inside the brackets
Case -1 :
Input : A1(1).B2(10).C345(100)
Desired output : 1.10.100 ( need to append '.' for each substring enclosed in brackets only)
case-2:
Input: A1(1).B22(23).C33(456)
Expected output : 1.23.456 ( need to append '.' for each substring enclosed in brackets only)
Current output with below code : 123456
(Below code is from example given by Pinal Dave at http://blog.sqlauthority.com/2008/10/14/sql-server-get-numeric-value-from-alpha-numeric-string-udf-for-get-numeric-numbers-only/ )
Declare @STR varchar(100)
set @STR='A(1).B(23).C(456)'
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @STR)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @STR= STUFF(@str, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @STR )
END
END
print @STR
END
Thanks,
Santosh kumar.
August 22, 2012 at 7:10 am
ssskumar4u (8/22/2012)
Hi,I have to peform a string manipulation as below:
Input: A(1).B(23).C(456)
Expected output : 1.23.456 ( need to append '.' for each substring)
Current output with below code : 123456
(Below code is from example given by Pinal Dave at http://blog.sqlauthority.com/2008/10/14/sql-server-get-numeric-value-from-alpha-numeric-string-udf-for-get-numeric-numbers-only/ )
Declare @STR varchar(100)
set @STR='A(1).B(23).C(456)'
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @STR)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @STR= STUFF(@str, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @STR )
END
END
print @STR
END
Thanks,
Santosh kumar.
Is this homework? Interview questions? Because getting someone else to provide you with a solution is unlikely to help you learn.
DECLARE @STR VARCHAR(256);
SET @STR = 'A(1).BC(10).DEF(100)';
WITH CTE(N) AS (SELECT 1 UNION ALL SELECT 1),
CTE2(N) AS (SELECT 1 FROM CTE x, CTE y),
CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),
CTE5(N) AS (SELECT 1 FROM CTE4 x, CTE4 y),
CTE6(N) AS (SELECT TOP(ISNULL(DATALENGTH(@str),0))
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE5)
SELECT (SELECT string + ''
FROM (SELECT CASE WHEN PATINDEX('[0-9]',SUBSTRING(@str,N,1)) > 0 OR PATINDEX('.',SUBSTRING(@str,N,1)) > 0
THEN SUBSTRING(@str,N,1) ELSE '' END
FROM CTE6) a(string)
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)');
August 22, 2012 at 7:15 am
Is this homework? Interview questions? Because getting someone else to provide you with a solution is unlikely to help you learn.
Especially not that solution! :w00t:
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 22, 2012 at 7:24 am
Phil Parkin (8/22/2012)
Is this homework? Interview questions? Because getting someone else to provide you with a solution is unlikely to help you learn.
Especially not that solution! :w00t:
Whoops? 😉
August 22, 2012 at 7:40 am
duplicate thread. Please direct all replies here. http://www.sqlservercentral.com/Forums/Topic1348317-392-1.aspx
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 22, 2012 at 7:59 am
I would do all this manipulations implementing CLR functions using Regex (static and compiled - as in an example I gave for your previous post).
The example solution provided (with WHILE loop) is the slowest possible way to achieve this and really can be used if you need to do it once (for one value only) - not as part of a query which affects multiple rows of data.
August 22, 2012 at 8:09 am
If you don't care much for performance and your input strings are always of the same shown pattern you can do:
DECLARE @s-2 VARCHAR(8000)
SET @s-2 = 'A1(1).B22(23).C33(456)'
SELECT REPLACE(
SUBSTRING(PARSENAME(@s,3),CHARINDEX('(',PARSENAME(@s,3))+1,8000)+ '.'
+ SUBSTRING(PARSENAME(@s,2),CHARINDEX('(',PARSENAME(@s,2))+1,8000)+ '.'
+ SUBSTRING(PARSENAME(@s,1),CHARINDEX('(',PARSENAME(@s,1))+1,8000)
,')','')
It will be still way better than the one with WHILE loop one
August 22, 2012 at 8:20 am
Cadavre (8/22/2012)
Phil Parkin (8/22/2012)
Is this homework? Interview questions? Because getting someone else to provide you with a solution is unlikely to help you learn.
Especially not that solution! :w00t:
Whoops? 😉
Maybe I jumped to the wrong conclusion - I didn't spend much time considering the question itself. I just liked the fact that you had used fairly advanced techniques to answer a potential 'homework' question.
I was imagining the situation where the newbie SQL student was asked to explain how that solution worked.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 22, 2012 at 9:20 am
Phil Parkin (8/22/2012)
Maybe I jumped to the wrong conclusion - I didn't spend much time considering the question itself. I just liked the fact that you had used fairly advanced techniques to answer a potential 'homework' question.I was imagining the situation where the newbie SQL student was asked to explain how that solution worked.
Yep, it's the reason why there are no comments or explanation of the code. It irks me when people ask for homework help without showing that they've tried themselves. When they post their own attempt at a solution, I'm happy enough to talk them through any issues they have with it. But with no effort from the OP, they get something complicated and unexplained from me - I'm a terrible human being, aren't I? :unsure:
August 22, 2012 at 9:36 am
Cadavre (8/22/2012)
Phil Parkin (8/22/2012)
Maybe I jumped to the wrong conclusion - I didn't spend much time considering the question itself. I just liked the fact that you had used fairly advanced techniques to answer a potential 'homework' question.I was imagining the situation where the newbie SQL student was asked to explain how that solution worked.
Yep, it's the reason why there are no comments or explanation of the code. It irks me when people ask for homework help without showing that they've tried themselves. When they post their own attempt at a solution, I'm happy enough to talk them through any issues they have with it. But with no effort from the OP, they get something complicated and unexplained from me - I'm a terrible human being, aren't I? :unsure:
You're not alone in this - I mean lack of comments, I won't be drawn into the terrible human being bit. It's amazing how few folks ask how the code works.
Edit: Phil says you are 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 22, 2012 at 9:40 am
ChrisM@Work (8/22/2012)
Cadavre (8/22/2012)
Phil Parkin (8/22/2012)
Maybe I jumped to the wrong conclusion - I didn't spend much time considering the question itself. I just liked the fact that you had used fairly advanced techniques to answer a potential 'homework' question.I was imagining the situation where the newbie SQL student was asked to explain how that solution worked.
Yep, it's the reason why there are no comments or explanation of the code. It irks me when people ask for homework help without showing that they've tried themselves. When they post their own attempt at a solution, I'm happy enough to talk them through any issues they have with it. But with no effort from the OP, they get something complicated and unexplained from me - I'm a terrible human being, aren't I? :unsure:
You're not alone in this - I mean lack of comments, I won't be drawn into the terrible human being bit. It's amazing how few folks ask how the code works.
Edit: Phil says you are 😉
Haha, oh no he does not - I'm right there with you both.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply