T-SQL String Manipulation- Need help

Question

Post reply

T-SQL String Manipulation- Need help

ssskumar4u

Mr or Mrs. 500

Points: 557
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: 323482 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 Mr or Mrs. 500 Points: 557 More actions · Answer 3

ssskumar4u

Mr or Mrs. 500

Points: 557

August 22, 2012 at 6:14 am

#1528574

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: 323482 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 Mr or Mrs. 500 Points: 557 More actions · Answer 6

ssskumar4u

Mr or Mrs. 500

Points: 557

August 22, 2012 at 7:10 am

#1528604

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 Mr or Mrs. 500 Points: 557 More actions · Answer 8

ssskumar4u

Mr or Mrs. 500

Points: 557

August 22, 2012 at 7:32 am

#1528629

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 VARCHAR(8000)

SET @S = '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