T-SQL String Manipulation- Need help

Question

Post reply

T-SQL String Manipulation- Need help

ssskumar4u

SSChasing Mays

Points: 607
More actions
August 22, 2012 at 5:34 am

#263208

Hi,
I have to manipulate string as below: ( integers placed in brackets should be replaced by n)
String manipulation shoud effect only the numbers inside the brackets
Case -1 :
Input : A1(1).B22(10).C345(100)
Desired output : A1(n).B22(n).C345(n)
Case-2:
Input : A22(1).BC23(10).DEF456(100)
Required Output: A22(n).BC23(n).DEF456(n)
current output with below code:
A(n).BC(nn).DEF(nnn)
DECLARE @STR VARCHAR(256)
SET @STR='A(1).BC(10).DEF(100)'
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[0-9]%', @STR)
BEGIN
WHILE @intAlpha > 0
BEGIN
print @intAlpha
SET @STR = STUFF(@str,@intAlpha, 1, 'n' )
SET @intAlpha = PATINDEX('%[0-9]%', @STR )
print @intAlpha
print @STR
END
END
Any help is appreciated.
Thanks,
santosh kumar.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you can do it with nested STUFF statements.

play with this till you understand it:

with MYCTE(val)

AS

(

SELECT 'A(1).BC(10).DEF(100)' UNION ALL

SELECT 'A(4).BC(34).DEF(754)' UNION ALL

SELECT 'A(9).BC(78).DEF(957)' UNION ALL

SELECT 'A(9) AND OTHER STUFF HERE '

)

SELECT

--just the first as an example

STUFF(val, PATINDEX('%([0-9])%',val),3, '(n)' ) As intAlpha,

--all three stuffs

STUFF(STUFF(STUFF(val, PATINDEX('%([0-9])%',val),3, '(n)' )

,PATINDEX('%([0-9][0-9])%',val),4, '(nn)' )

,PATINDEX('%([0-9][0-9][0-9])%',val),5, '(nnn)' ) As FinalVal,

*

FROM MyCTE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 2

My (dirty) solution: Add a couple of REPLACEs at the end of your code, outside the loops:

SET @STR = REPLACE(@str,'nn','n')

SET @STR = REPLACE(@str,'nnn','n')

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

ssskumar4u SSChasing Mays Points: 607 More actions · Answer 3

Hi Lowel,

Thanks for immediate response.

Still same output from your code:

A(n).BC(nn).DEF(nnn)

Expected output : A(n).BC(n).DEF(n)

( whatever the number enclosed in brackets should be replaced with single 'n')

Thanks,

santosh kumar

Lowell SSC Guru Points: 323518 More actions · Answer 4

ahh i missed that somehow;

i think Adding Dereks idea, just a couple of nested REPLACE functions to change (nnn) to (n) and (nn) to (n) would be the easiest.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

Different approach: -

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 REPLACE(((SELECT string + ''

FROM (SELECT CASE WHEN PATINDEX('[0-9]',SUBSTRING(@str,ISNULL(NULLIF(N-1,0),1),1)) > 0

AND PATINDEX(')',SUBSTRING(@str,N,1)) = 0

THEN ''

WHEN PATINDEX('[0-9]',SUBSTRING(@str,N,1)) > 0

THEN '$'

ELSE SUBSTRING(@str,N,1) END

FROM CTE6) a(string)

FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)')

) COLLATE Latin1_General_BIN2, '$','n');

This uses the assumption that you'll never have a $ symbol in your string. It replicates your loop by using an inline numbers CTE.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ssskumar4u SSChasing Mays Points: 607 More actions · Answer 6

Hi Cadavre,

Thanks for your great help.

I observed that for this input :

@STR = 'A1(1).B2(2)'

output from your code : Ann).Bnn)

Desired output : A1(n).B2(n)

String manipulation shoud effect only the numbers inside the brackets.

Thanks,

santosh kumar.

Cadavre SSC-Forever Points: 41690 More actions · Answer 7

ssskumar4u (8/22/2012)
Hi Cadavre,
Thanks for your great help.
I observed that for this input :
@STR = 'A1(1).B2(2)'
output from your code : Ann).Bnn)
Desired output : A1(n).B2(n)
String manipulation shoud effect only the numbers inside the brackets.
Thanks,
santosh kumar.

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 REPLACE(((SELECT string + ''

FROM (SELECT CASE WHEN PATINDEX('[0-9]',SUBSTRING(@str,ISNULL(NULLIF(N-1,0),1),1)) > 0

AND PATINDEX('(',SUBSTRING(@str,N,1)) = 0

AND PATINDEX(')',SUBSTRING(@str,N,1)) = 0

THEN ''

WHEN PATINDEX('(',SUBSTRING(@str,ISNULL(NULLIF(N-1,0),1),1)) > 0

AND PATINDEX('[0-9]',SUBSTRING(@str,N,1)) > 0

THEN '$'

ELSE SUBSTRING(@str,N,1) END

FROM CTE6) a(string)

FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)')

) COLLATE Latin1_General_BIN2, '$','n');

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ssskumar4u SSChasing Mays Points: 607 More actions · Answer 8

Hi Cadavre,

Thanks for providing the solution.

observed case :

Input : A1(1).B22(10).C345(100)

received output from your code : A1(1).B2(10).C3(100)

Desired output : A1(n).B22(n).C345(n)

output is giving only one integer from each substring enclosed outside the brackets.

Thanks,

santosh.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

You may consider using CLR function:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

namespace CLRPlay

{

public partial class UserDefinedFunctions

{

static readonly Regex _regex = new Regex(@"[(][0-9]*[)]", RegexOptions.Compiled);

static readonly string _nR = @"(n)";

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString ReplaceNumbersInBrackets(SqlString val)

{

if (val.IsNull) return SqlString.Null;

return _regex.Replace(val.ToString(), _nR);

}

};

}

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

SELECT

Stringy,

Stuffy = STUFF(STUFF(STUFF(Stringy,x3.startpos+1,x3.endpos-x3.startpos-1,'n'),x2.startpos+1,x2.endpos-x2.startpos-1,'n'),x1.startpos+1,x1.endpos-x1.startpos-1,'n')

FROM (SELECT Stringy = 'A1(1).B22(10).C345(100)') d

CROSS APPLY (SELECT startpos = CHARINDEX('(',Stringy,1), endpos = CHARINDEX(').',Stringy+'.',1)) x1

CROSS APPLY (SELECT startpos = CHARINDEX('(',Stringy,x1.startpos+1), endpos = CHARINDEX(').',Stringy+'.',x1.endpos+1)) x2

CROSS APPLY (SELECT startpos = CHARINDEX('(',Stringy,x2.startpos+1), endpos = CHARINDEX(').',Stringy+'.',x2.endpos+1)) x3

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 11

DECLARE @STR VARCHAR(256) = 'A1(1).B22(10).C345(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),

CTE7(N) AS (SELECT 1 UNION ALL

SELECT N+1 FROM CTE6

WHERE SUBSTRING(@str,N,1) = '('),

CTE8(N,X) AS (SELECT N,

ISNULL(NULLIF(CHARINDEX(')',@str,N),0)-N,8000)

FROM CTE7),

CTE9(N,X) AS (SELECT '('+SUBSTRING(@str, N, X)+')',

ROW_NUMBER() OVER(ORDER BY CAST(SUBSTRING(@str, N, X) AS INT) ASC)

FROM CTE8

WHERE ISNUMERIC(SUBSTRING(@str, N, X)) = 1),

CTE10(N, X, Y) AS (SELECT TOP 1 REPLACE(@str,N,'(n)'), X, 1

FROM CTE9

ORDER BY X DESC

UNION ALL

SELECT REPLACE(a.N, b.N, '(n)'), b.X, a.Y+1

FROM CTE10 a

CROSS JOIN CTE9 b

WHERE b.X < a.X)

SELECT N

FROM CTE10 a

INNER JOIN (SELECT MAX(Y) FROM CTE10) b(Y) ON a.Y = b.Y;

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Cadavre SSC-Forever Points: 41690 More actions · Answer 12

ChrisM@Work (8/22/2012)
SELECT Stringy, Stuffy = STUFF(STUFF(STUFF(Stringy,x3.startpos+1,x3.endpos-x3.startpos-1,'n'),x2.startpos+1,x2.endpos-x2.startpos-1,'n'),x1.startpos+1,x1.endpos-x1.startpos-1,'n') FROM (SELECT Stringy = 'A1(1).B22(10).C345(100)') d CROSS APPLY (SELECT startpos = CHARINDEX('(',Stringy,1), endpos = CHARINDEX(').',Stringy+'.',1)) x1 CROSS APPLY (SELECT startpos = CHARINDEX('(',Stringy,x1.startpos+1), endpos = CHARINDEX(').',Stringy+'.',x1.endpos+1)) x2 CROSS APPLY (SELECT startpos = CHARINDEX('(',Stringy,x2.startpos+1), endpos = CHARINDEX(').',Stringy+'.',x2.endpos+1)) x3

Very clever. I hadn't thought of appending a period to the end of the string to do the split.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 13

Again here, 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

LEFT(PARSENAME(@s,3),CHARINDEX('(',PARSENAME(@s,3)))+ 'n).'

+ LEFT(PARSENAME(@s,2),CHARINDEX('(',PARSENAME(@s,2)))+ 'n).'

+ LEFT(PARSENAME(@s,1),CHARINDEX('(',PARSENAME(@s,1)))+'n)'

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

Cadavre (8/22/2012)
ChrisM@Work (8/22/2012)
SELECT Stringy, Stuffy = STUFF(STUFF(STUFF(Stringy,x3.startpos+1,x3.endpos-x3.startpos-1,'n'),x2.startpos+1,x2.endpos-x2.startpos-1,'n'),x1.startpos+1,x1.endpos-x1.startpos-1,'n') FROM (SELECT Stringy = 'A1(1).B22(10).C345(100)') d CROSS APPLY (SELECT startpos = CHARINDEX('(',Stringy,1), endpos = CHARINDEX(').',Stringy+'.',1)) x1 CROSS APPLY (SELECT startpos = CHARINDEX('(',Stringy,x1.startpos+1), endpos = CHARINDEX(').',Stringy+'.',x1.endpos+1)) x2 CROSS APPLY (SELECT startpos = CHARINDEX('(',Stringy,x2.startpos+1), endpos = CHARINDEX(').',Stringy+'.',x2.endpos+1)) x3
Very clever. I hadn't thought of appending a period to the end of the string to do the split.

Thanks. I added a couple of extra ones in to make sure you'd notice 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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