I'm hoping for a bit of assistance, I know this is probably easy but just having a hard time solving it.
What I'm hoping to achieve is the string of '000-0000-0000-0000-000000000000' gets changed to the values of
'000-0000-0000-0000-000000000001'
'000-0000-0000-0000-000000000099'
'000-0000-0000-0000-000000000888'
'000-0000-0000-0000-0000000005555'
Any help would be appreciated
DROP TABLE IF EXISTS #test
CREATE TABLE #test ([ValueID] VARCHAR)
INSERT INTO #test (ValueID)
VALUES
(1),
(99),
(888),
(5555)
-- n
DECLARE @GuidString VARCHAR(36) = '00000000-0000-0000-0000-000000000000'
SELECT
ValueID,
GuidString = @GuidString,
NewGuidString = 'expected value'
FROM
#test
November 2, 2023 at 6:16 am
At the moment, I've got something like this which returns me the Length of the Value I need replacing, e.g. 1 number , 2 numbers, n.... etc
DROP TABLE IF EXISTS #test
CREATE TABLE #test ([ValueID] VARCHAR(10))
INSERT INTO #test (ValueID)
VALUES
(1),
(99),
(888),
(5555)
-- n
DECLARE @GuidString VARCHAR(36) = '00000000-0000-0000-0000-000000000000'
SELECT
ValueID,
GuidString = @GuidString,
NewGuidString = RIGHT(@GuidString,LEN(ValueID))
FROM
#test
Try this, noting that
a) Varchars should always be declared with an explicit length
b) When inserting literal varchars, surround them with single quotes
DROP TABLE IF EXISTS #test;
CREATE TABLE #test
(
ValueId VARCHAR(30)
);
INSERT #test
(
ValueId
)
VALUES
('1')
,('99')
,('888')
,('5555');
DECLARE @GuidString VARCHAR(36) = '00000000-0000-0000-0000-000000000000';
SELECT ValueId
,GuidString = @GuidString
,NewGuidString = CONCAT (LEFT(@GuidString, 36 - LEN (ValueId)), ValueId)
FROM #test;
November 2, 2023 at 10:06 am
That's exactly what I was After... I need there was an easier way than doing substring, reverse, replace etc.
November 2, 2023 at 10:15 am
BUT, that will only work if LEN(ValueID) <= 12
For longer strings, you will need to break ValueId into parts to match the GUID pattern
November 2, 2023 at 10:22 am
BUT, that will only work if LEN(ValueID) <= 12 For longer strings, you will need to break ValueId into parts to match the GUID pattern
The value is actually an INT from a lookup table so wont be more than maybe 1000 records, I just did VARCHAR to simplify it but will do the conversions now I have it.
November 2, 2023 at 10:25 am
DesNorton wrote:BUT, that will only work if LEN(ValueID) <= 12 For longer strings, you will need to break ValueId into parts to match the GUID pattern
The value is actually an INT from a lookup table so wont be more than maybe 1000 records, I just did VARCHAR to simplify it but will do the conversions now I have it.
CONCAT will do the conversions for you.
DROP TABLE IF EXISTS #test;
CREATE TABLE #test
(
ValueId int
);
INSERT #test
(
ValueId
)
VALUES
(1)
,(99)
,(888)
,(5555);
DECLARE @GuidString VARCHAR(36) = '00000000-0000-0000-0000-000000000000';
SELECT ValueId
,GuidString = @GuidString
,NewGuidString = CONCAT (LEFT(@GuidString, 36 - LEN (ValueId)), ValueId)
FROM #test;
November 2, 2023 at 10:26 am
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;
November 2, 2023 at 10:34 am
Tava wrote:DesNorton wrote:BUT, that will only work if LEN(ValueID) <= 12 For longer strings, you will need to break ValueId into parts to match the GUID pattern
The value is actually an INT from a lookup table so wont be more than maybe 1000 records, I just did VARCHAR to simplify it but will do the conversions now I have it.
CONCAT will do the conversions for you.
But its an Implicit conversion, wouldn't it be better to explicitly convert the value to Varchar.
November 2, 2023 at 10:36 am
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.
November 2, 2023 at 10:50 am
But its an Implicit conversion, wouldn't it be better to explicitly convert the value to Varchar.
Usually, I'd agree with you on explicit vs implicit conversions. But here is a quote from Microsoft's help on CONCAT
'CONCAT implicitly converts all arguments to string types before concatenation.'
So in this case, adding a CAST/CONVERT is redundant.
November 2, 2023 at 10:59 am
Tava wrote:But its an Implicit conversion, wouldn't it be better to explicitly convert the value to Varchar.
Usually, I'd agree with you on explicit vs implicit conversions. But here is a quote from Microsoft's help on CONCAT
'CONCAT implicitly converts all arguments to string types before concatenation.'
So in this case, adding a CAST/CONVERT is redundant.
Interesting, thanks for that explanation
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy