Derived column

Question

Post reply

Derived column

komal145

SSCrazy Eights

Points: 9918
More actions
August 24, 2012 at 12:02 pm

#260547

i have column value something like below.
{784A4579-8689-438E-ADAA-9DCBC8A88AE7}
I just need "784A4579-8689-438E-ADAA-9DCBC8A88AE7" i.e value between {}
can any one help me with this?
thanks!!

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

nested replace would probably be the easiest:;

you could substring it as well, because the desired string, including dashes will be 36 chars.

SELECT REPLACE(REPLACE('{784A4579-8689-438E-ADAA-9DCBC8A88AE7}','{',''),'}',''),

SUBSTRING('{784A4579-8689-438E-ADAA-9DCBC8A88AE7}',2,36)

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!

dan-572483 SSCoach Points: 15870 More actions · Answer 2

If the length of the string might vary - but there will always be brackets - this will work:

declare @string varchar(max)

set @string = '{String you want to remove from the brackets}'

select SUBSTRING(@string, CHARINDEX('{',@string)+1,Charindex('}',@string)-2)

rVadim Hall of Fame Points: 3969 More actions · Answer 3

If this is actually a GUID then you can also do this:

DECLARE @UST VARCHAR(38) = '{784A4579-8689-438E-ADAA-9DCBC8A88AE7}';

SELECT CAST(CAST(@UST AS UNIQUEIDENTIFIER) AS VARCHAR(36));

--Vadim R.

demonfox SSCertifiable Points: 6289 More actions · Answer 4

Another way to do it using STUFF.

declare @testid varchar(50)=

'{784A4579-8689-438E-ADAA-9DCBC8A88AE7}'

select STUFF(stuff(@testid,len(@testid),len(@testid),''),1,1,'')

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

gravitysucks Ten Centuries Points: 1122 More actions · Answer 5

dan-572483 (8/24/2012)
If the length of the string might vary - but there will always be brackets - this will work:
declare @string varchar(max)
set @string = '{String you want to remove from the brackets}'
select SUBSTRING(@string, CHARINDEX('{',@string)+1,Charindex('}',@string)-2)

Dan,

just out of curiousity-whats the logic behind using '-2' instead of '-1' with charindex.You need to use-2 even if you use len(@string). Whats the logic? I am guessing its because of +1 at the start position-but why? Thanks.

demonfox SSCertifiable Points: 6289 More actions · Answer 6

gravitysucks (8/25/2012)
dan-572483 (8/24/2012)
If the length of the string might vary - but there will always be brackets - this will work:
declare @string varchar(max)
set @string = '{String you want to remove from the brackets}'
select SUBSTRING(@string, CHARINDEX('{',@string)+1,Charindex('}',@string)-2)
Dan,
just out of curiousity-whats the logic behind using '-2' instead of '-1' with charindex.You need to use-2 even if you use len(@string). Whats the logic? I am guessing its because of +1 at the start position-but why? Thanks.

the actual syntax is :

SUBSTRING ( expression ,start , length )

here the length is how many characters of the expression is returned ; i.e the new string length expected.

as CharIndex on } gives the total length of the initial string, -2 is done to get total length of new string.

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

dan-572483 SSCoach Points: 15870 More actions · Answer 7

I realized that if erroneous text were in your column, my code might produce an incorrect result. Here is an improved version:

declare @string varchar(max)

declare @openbracket int

declare @closebracket int

set @string = 'extra{Text You Want}text'

set @openbracket = charindex('{',@string)

set @closebracket = charindex('}',@string)

IF @openbracket >0 and @closebracket >0

BEGIN

select SUBSTRING(@string, @openbracket +1, @closebracket-@openbracket-1) as 'Result'

End

Else

Select 'Invalid Data' as 'Result'