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;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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 21 total)
You must be logged in to reply to this topic. Login to reply