Replacing last few characters with another value.

Question

Post reply

Replacing last few characters with another value.

November 16, 2023 at 6:09 am

This was removed by the editor as SPAM
November 16, 2023 at 6:16 am

This was removed by the editor as SPAM

Viewing 7 posts - 16 through 21 (of 21 total)

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

DesNorton SSC-Insane Points: 24387 More actions · Answer 1

Tava wrote:

DesNorton wrote:

DROP TABLE IF EXISTS #test;
CREATE TABLE #test ( ValueId varchar(30));

INSERT  #test ( ValueId )
VALUES ( '1' )
     , ( '99' )
     , ( '888' )
     , ( '5555' )
     , ( '123456789012' )
     , ( '12345678901234' )
     , ( '1234567890123456789' )
     , ( '12345678901234567890123456789' );

DECLARE @GuidString varchar(36) = '00000000-0000-0000-0000-000000000000';

SELECT  ValueId
      , GuidString    = @GuidString
      --, NewGuidString = CONCAT( LEFT(@GuidString, 36 - LEN( ValueId )), ValueId )
      , NewGuidString = STUFF(STUFF(STUFF(STUFF(RIGHT('00000000000000000000000000000000' + ValueId, 32), 9, 0,'-'), 14, 0, '-'), 19, 0, '-'), 24, 0, '-')
FROM    #test;

Question, why would one use the STUFF function over the CONCAT.

From a readability/understanding code POV its a lot more complex.

The CONCAT works because you will never get to 12 characters (INT has only 10 digits).

IF it was BIGINT, there would be up to 20 characters. In that case, you would lose the formatting, making the string unable to convert to GUID.

How to post data/code on a forum to get the best help.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

"Bumping" this post because it's hidden by the "Don't display the first post of a new page" problem this forum has, which I wish they'd fix. This has been a reported issue for years, now. 🙁

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Although there is a solution provided to the OP that works, I have to ask what the original reason is for trying to build a string based GUID base on integer values from somewhere.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tava SSCarpal Tunnel Points: 4122 More actions · Answer 4

Jeff Moden wrote:

Although there is a solution provided to the OP that works, I have to ask what the original reason is for trying to build a string based GUID base on integer values from somewhere.

very good question, very silly answer lol....

We are migrating data from one customers solution to another customers solution and the target solution cannot be changed (doesn't want to be changed) and must have one of the values we are passing as a GUID. However the value from the source is an INT.

The source doesn't want to add a GUID to the table, and the target doesn't want to create a GUID on import.

Hence the solution above - created a new GUID using NewID and then using the PrimaryKey from the source table to change the final characters.

This is just a one of migration, and explained to everyone involved, that this isn't the right way.

Hopefully that helps.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Tava wrote:

Jeff Moden wrote:
Although there is a solution provided to the OP that works, I have to ask what the original reason is for trying to build a string based GUID base on integer values from somewhere.
very good question, very silly answer lol....
We are migrating data from one customers solution to another customers solution and the target solution cannot be changed (doesn't want to be changed) and must have one of the values we are passing as a GUID. However the value from the source is an INT.
The source doesn't want to add a GUID to the table, and the target doesn't want to create a GUID on import.
Hence the solution above - created a new GUID using NewID and then using the PrimaryKey from the source table to change the final characters.
This is just a one of migration, and explained to everyone involved, that this isn't the right way.
Hopefully that helps.

Thanks for taking the time to explain, Tava. I really appreciate it. It just had to be something like that or special "service GUIDs".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)