Mrthfx

How would an AI self awareness kill switch work?

One Half of Ten; A Riddle

Why has the mole been redefined for 2019?

Could a phylactery of a lich be a mirror or does it have to be a box?

How can my powered armor quickly replace its ceramic plates?

Can we use the stored gravitational potential energy of a building to produce power?

Publishing research using outdated methods

Is boss over stepping boundary/micromanaging?

Cookies - Should the toggles be on?

How much mayhem could I cause as a sentient fish?

How to say "Brexit" in Latin?

Do authors have to be politically correct in article-writing?

How can I deliver in-universe written lore to players without it being dry exposition?

Can I write a book of my D&D game?

What is the purpose of easy combat scenarios that don't need resource expenditure?

If I delete my router's history can my ISP still provide it to my parents?

what does しにみえてる mean?

Why Prushim were the ones who "separated"?

Caruana vs Carlsen game 10 (WCC) why not 18...Nxb6?

Pronunciation of umlaut vowels in the history of German

How to deal with an incendiary email that was recalled

How to limit sight distance to 1 KM

Why is working on the same position for more than 15 years not a red flag?

What are "industrial chops"?

reverse the characters of a string without changing the position of the numeric value in T-SQL

How can I strip non-numeric characters out of a string?CAST SUBSTRING to NUMERIC SSISCASE with CHARINDEX and CONVERTSQL Query not throwing error even if the entered scale value is higher than specifiedHow to write the SQL Query to find different table valueFiltering characters to just return numeric characters in a string?Microsoft SQL Server SQL Injection Through .NET Even With Quote EscapingReplace Character Without Using Looped REPLACE FunctionSubstring without the first n charactersHow to increment identity without add a row and get the skipped value?

2

1

With T-SQL, I'm trying to find the easiest way to reverse the characters of a string without changing the position of the numeric value.

So for string:

abc223de11 

have

edc223ba11

sql-server

share|improve this question

edited 4 hours ago

McNets

16k42161

asked 4 hours ago

annaanna

162

New contributor

anna is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Reversing strings is surprisingly tricky if there are Unicode characters. T-SQL is poorly suited for such a task. Would it be possible to use, say, .Net instead? Either on application level or via CLR?
  
  – vonPryz
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  And to ask a silly question, why? Also, what version of SQL Server?
  
  – Aaron Bertrand♦
  3 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm using SSMS17, It's just for my personal training I just came up with this scenario to test out my t-sql skills to see if it's possible to solve it with t-sql. Because in other languages we could possibly swap characters within string easily and quickly but I couldn't do it with t-sql.
  
  – anna
  2 hours ago
  

  
  
  
  

add a comment | 

2

1

With T-SQL, I'm trying to find the easiest way to reverse the characters of a string without changing the position of the numeric value.

So for string:

abc223de11 

have

edc223ba11

sql-server

share|improve this question

edited 4 hours ago

McNets

16k42161

asked 4 hours ago

annaanna

162

New contributor

anna is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Reversing strings is surprisingly tricky if there are Unicode characters. T-SQL is poorly suited for such a task. Would it be possible to use, say, .Net instead? Either on application level or via CLR?
  
  – vonPryz
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  And to ask a silly question, why? Also, what version of SQL Server?
  
  – Aaron Bertrand♦
  3 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm using SSMS17, It's just for my personal training I just came up with this scenario to test out my t-sql skills to see if it's possible to solve it with t-sql. Because in other languages we could possibly swap characters within string easily and quickly but I couldn't do it with t-sql.
  
  – anna
  2 hours ago
  

  
  
  
  

add a comment | 

With T-SQL, I'm trying to find the easiest way to reverse the characters of a string without changing the position of the numeric value.

So for string:

abc223de11 

have

edc223ba11

sql-server

share|improve this question

edited 4 hours ago

McNets

16k42161

asked 4 hours ago

annaanna

162

New contributor

anna is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

abc223de11 

edc223ba11

share|improve this question

edited 4 hours ago

McNets

16k42161

asked 4 hours ago

annaanna

162

New contributor

anna is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 4 hours ago

McNets

16k42161

asked 4 hours ago

annaanna

162

New contributor

anna is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited 4 hours ago

McNets

16k42161

edited 4 hours ago

McNets

16k42161

asked 4 hours ago

annaanna

162

New contributor

anna is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 4 hours ago

annaanna

162

1 Answer
1

active

oldest

votes

5

I am not proud of this, but you can do it in T-SQL. This inline table-valued function breaks the string into a set of characters, and applies row numbers to just the string values, so that you can flip the set upside down.

CREATE FUNCTION dbo.SortString

(

  @s varchar(64)

)

RETURNS TABLE WITH SCHEMABINDING

AS

  RETURN 

  ( -- get a sequence number for every character in @s

    WITH n AS 

    (

      SELECT n = 1 UNION ALL SELECT n + 1 FROM n WHERE n < LEN(@s)

    ),

    s AS 

    ( -- break out each character, apply sequence number, test numeric

      SELECT n, s = SUBSTRING(@s, n, 1), isn = ISNUMERIC(SUBSTRING(@s, n, 1))

      FROM n

    ),

    s2 AS

    ( -- apply reverse match pointers, but only for strings

      SELECT n,s, 

        rn1 = CASE WHEN isn = 0 THEN ROW_NUMBER() OVER 

              (PARTITION BY isn ORDER BY n ASC) END,

        rn2 = CASE WHEN isn = 0 THEN ROW_NUMBER() OVER 

              (PARTITION BY isn ORDER BY n DESC) END

      FROM s

    )

    SELECT s2.n, New = COALESCE(s3.s, s2.s), Original = s2.s

        FROM s2 LEFT OUTER JOIN s2 AS s3

        ON s2.rn2 = s3.rn1

  );

GO

The following call:

DECLARE @str varchar(64) = 'abc223de11'; 

SELECT Original, New FROM dbo.SortString(@str) ORDER BY n;

Yields the following results:

Original  New

--------  ---

a         e

b         d

c         c

2         2

2         2

3         3

d         b

e         a

1         1

1         1

In SQL Server 2017, you can pack the string back together with predictable order using STRING_AGG():

DECLARE @str varchar(64) = 'abc223de11'; 



SELECT 

  OriginalString = @str, 

  QuasiReversed = STRING_AGG(New,'') WITHIN GROUP (ORDER BY n) 

FROM dbo.SortString(@str);

In older versions, you'll need to use a FOR XML hack:

DECLARE @str varchar(64) = 'abc223de11'; 



SELECT 

  OriginalString = @str, 

  QuasiReversed = (SELECT '' + New

    FROM dbo.SortString(@str) 

    ORDER BY n

    FOR XML PATH, TYPE).value(N'.[1]','varchar(64)');

Results in both cases:

OriginalString  QuasiReversed

--------------  -------------

abc223de11      edc223ba11

share|improve this answer

answered 3 hours ago

Aaron Bertrand♦Aaron Bertrand

152k18289489

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

anna is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230976%2freverse-the-characters-of-a-string-without-changing-the-position-of-the-numeric%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

5

I am not proud of this, but you can do it in T-SQL. This inline table-valued function breaks the string into a set of characters, and applies row numbers to just the string values, so that you can flip the set upside down.

CREATE FUNCTION dbo.SortString

(

  @s varchar(64)

)

RETURNS TABLE WITH SCHEMABINDING

AS

  RETURN 

  ( -- get a sequence number for every character in @s

    WITH n AS 

    (

      SELECT n = 1 UNION ALL SELECT n + 1 FROM n WHERE n < LEN(@s)

    ),

    s AS 

    ( -- break out each character, apply sequence number, test numeric

      SELECT n, s = SUBSTRING(@s, n, 1), isn = ISNUMERIC(SUBSTRING(@s, n, 1))

      FROM n

    ),

    s2 AS

    ( -- apply reverse match pointers, but only for strings

      SELECT n,s, 

        rn1 = CASE WHEN isn = 0 THEN ROW_NUMBER() OVER 

              (PARTITION BY isn ORDER BY n ASC) END,

        rn2 = CASE WHEN isn = 0 THEN ROW_NUMBER() OVER 

              (PARTITION BY isn ORDER BY n DESC) END

      FROM s

    )

    SELECT s2.n, New = COALESCE(s3.s, s2.s), Original = s2.s

        FROM s2 LEFT OUTER JOIN s2 AS s3

        ON s2.rn2 = s3.rn1

  );

GO

The following call:

DECLARE @str varchar(64) = 'abc223de11'; 

SELECT Original, New FROM dbo.SortString(@str) ORDER BY n;

Yields the following results:

Original  New

--------  ---

a         e

b         d

c         c

2         2

2         2

3         3

d         b

e         a

1         1

1         1

In SQL Server 2017, you can pack the string back together with predictable order using STRING_AGG():

DECLARE @str varchar(64) = 'abc223de11'; 



SELECT 

  OriginalString = @str, 

  QuasiReversed = STRING_AGG(New,'') WITHIN GROUP (ORDER BY n) 

FROM dbo.SortString(@str);

In older versions, you'll need to use a FOR XML hack:

DECLARE @str varchar(64) = 'abc223de11'; 



SELECT 

  OriginalString = @str, 

  QuasiReversed = (SELECT '' + New

    FROM dbo.SortString(@str) 

    ORDER BY n

    FOR XML PATH, TYPE).value(N'.[1]','varchar(64)');

Results in both cases:

OriginalString  QuasiReversed

--------------  -------------

abc223de11      edc223ba11

share|improve this answer

answered 3 hours ago

Aaron Bertrand♦Aaron Bertrand

152k18289489

add a comment | 

5

I am not proud of this, but you can do it in T-SQL. This inline table-valued function breaks the string into a set of characters, and applies row numbers to just the string values, so that you can flip the set upside down.

CREATE FUNCTION dbo.SortString

(

  @s varchar(64)

)

RETURNS TABLE WITH SCHEMABINDING

AS

  RETURN 

  ( -- get a sequence number for every character in @s

    WITH n AS 

    (

      SELECT n = 1 UNION ALL SELECT n + 1 FROM n WHERE n < LEN(@s)

    ),

    s AS 

    ( -- break out each character, apply sequence number, test numeric

      SELECT n, s = SUBSTRING(@s, n, 1), isn = ISNUMERIC(SUBSTRING(@s, n, 1))

      FROM n

    ),

    s2 AS

    ( -- apply reverse match pointers, but only for strings

      SELECT n,s, 

        rn1 = CASE WHEN isn = 0 THEN ROW_NUMBER() OVER 

              (PARTITION BY isn ORDER BY n ASC) END,

        rn2 = CASE WHEN isn = 0 THEN ROW_NUMBER() OVER 

              (PARTITION BY isn ORDER BY n DESC) END

      FROM s

    )

    SELECT s2.n, New = COALESCE(s3.s, s2.s), Original = s2.s

        FROM s2 LEFT OUTER JOIN s2 AS s3

        ON s2.rn2 = s3.rn1

  );

GO

The following call:

DECLARE @str varchar(64) = 'abc223de11'; 

SELECT Original, New FROM dbo.SortString(@str) ORDER BY n;

Yields the following results:

Original  New

--------  ---

a         e

b         d

c         c

2         2

2         2

3         3

d         b

e         a

1         1

1         1

In SQL Server 2017, you can pack the string back together with predictable order using STRING_AGG():

DECLARE @str varchar(64) = 'abc223de11'; 



SELECT 

  OriginalString = @str, 

  QuasiReversed = STRING_AGG(New,'') WITHIN GROUP (ORDER BY n) 

FROM dbo.SortString(@str);

In older versions, you'll need to use a FOR XML hack:

DECLARE @str varchar(64) = 'abc223de11'; 



SELECT 

  OriginalString = @str, 

  QuasiReversed = (SELECT '' + New

    FROM dbo.SortString(@str) 

    ORDER BY n

    FOR XML PATH, TYPE).value(N'.[1]','varchar(64)');

Results in both cases:

OriginalString  QuasiReversed

--------------  -------------

abc223de11      edc223ba11

share|improve this answer

answered 3 hours ago

Aaron Bertrand♦Aaron Bertrand

152k18289489

add a comment | 

I am not proud of this, but you can do it in T-SQL. This inline table-valued function breaks the string into a set of characters, and applies row numbers to just the string values, so that you can flip the set upside down.

CREATE FUNCTION dbo.SortString

(

  @s varchar(64)

)

RETURNS TABLE WITH SCHEMABINDING

AS

  RETURN 

  ( -- get a sequence number for every character in @s

    WITH n AS 

    (

      SELECT n = 1 UNION ALL SELECT n + 1 FROM n WHERE n < LEN(@s)

    ),

    s AS 

    ( -- break out each character, apply sequence number, test numeric

      SELECT n, s = SUBSTRING(@s, n, 1), isn = ISNUMERIC(SUBSTRING(@s, n, 1))

      FROM n

    ),

    s2 AS

    ( -- apply reverse match pointers, but only for strings

      SELECT n,s, 

        rn1 = CASE WHEN isn = 0 THEN ROW_NUMBER() OVER 

              (PARTITION BY isn ORDER BY n ASC) END,

        rn2 = CASE WHEN isn = 0 THEN ROW_NUMBER() OVER 

              (PARTITION BY isn ORDER BY n DESC) END

      FROM s

    )

    SELECT s2.n, New = COALESCE(s3.s, s2.s), Original = s2.s

        FROM s2 LEFT OUTER JOIN s2 AS s3

        ON s2.rn2 = s3.rn1

  );

GO

The following call:

DECLARE @str varchar(64) = 'abc223de11'; 

SELECT Original, New FROM dbo.SortString(@str) ORDER BY n;

Yields the following results:

Original  New

--------  ---

a         e

b         d

c         c

2         2

2         2

3         3

d         b

e         a

1         1

1         1

In SQL Server 2017, you can pack the string back together with predictable order using STRING_AGG():

DECLARE @str varchar(64) = 'abc223de11'; 



SELECT 

  OriginalString = @str, 

  QuasiReversed = STRING_AGG(New,'') WITHIN GROUP (ORDER BY n) 

FROM dbo.SortString(@str);

In older versions, you'll need to use a FOR XML hack:

DECLARE @str varchar(64) = 'abc223de11'; 



SELECT 

  OriginalString = @str, 

  QuasiReversed = (SELECT '' + New

    FROM dbo.SortString(@str) 

    ORDER BY n

    FOR XML PATH, TYPE).value(N'.[1]','varchar(64)');

Results in both cases:

OriginalString  QuasiReversed

--------------  -------------

abc223de11      edc223ba11

share|improve this answer

answered 3 hours ago

Aaron Bertrand♦Aaron Bertrand

152k18289489

CREATE FUNCTION dbo.SortString

(

  @s varchar(64)

)

RETURNS TABLE WITH SCHEMABINDING

AS

  RETURN 

  ( -- get a sequence number for every character in @s

    WITH n AS 

    (

      SELECT n = 1 UNION ALL SELECT n + 1 FROM n WHERE n < LEN(@s)

    ),

    s AS 

    ( -- break out each character, apply sequence number, test numeric

      SELECT n, s = SUBSTRING(@s, n, 1), isn = ISNUMERIC(SUBSTRING(@s, n, 1))

      FROM n

    ),

    s2 AS

    ( -- apply reverse match pointers, but only for strings

      SELECT n,s, 

        rn1 = CASE WHEN isn = 0 THEN ROW_NUMBER() OVER 

              (PARTITION BY isn ORDER BY n ASC) END,

        rn2 = CASE WHEN isn = 0 THEN ROW_NUMBER() OVER 

              (PARTITION BY isn ORDER BY n DESC) END

      FROM s

    )

    SELECT s2.n, New = COALESCE(s3.s, s2.s), Original = s2.s

        FROM s2 LEFT OUTER JOIN s2 AS s3

        ON s2.rn2 = s3.rn1

  );

GO

DECLARE @str varchar(64) = 'abc223de11'; 

SELECT Original, New FROM dbo.SortString(@str) ORDER BY n;

Original  New

--------  ---

a         e

b         d

c         c

2         2

2         2

3         3

d         b

e         a

1         1

1         1

DECLARE @str varchar(64) = 'abc223de11'; 



SELECT 

  OriginalString = @str, 

  QuasiReversed = STRING_AGG(New,'') WITHIN GROUP (ORDER BY n) 

FROM dbo.SortString(@str);

DECLARE @str varchar(64) = 'abc223de11'; 



SELECT 

  OriginalString = @str, 

  QuasiReversed = (SELECT '' + New

    FROM dbo.SortString(@str) 

    ORDER BY n

    FOR XML PATH, TYPE).value(N'.[1]','varchar(64)');

OriginalString  QuasiReversed

--------------  -------------

abc223de11      edc223ba11

share|improve this answer

answered 3 hours ago

Aaron Bertrand♦Aaron Bertrand

152k18289489

answered 3 hours ago

Aaron Bertrand♦Aaron Bertrand

152k18289489

answered 3 hours ago

Aaron Bertrand♦Aaron Bertrand

152k18289489