Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Derived column Expand / Collapse
Author
Message
Posted Friday, August 24, 2012 12:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 2:07 PM
Points: 171, Visits: 556
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!!

Post #1349853
Posted Friday, August 24, 2012 12:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1349858
Posted Friday, August 24, 2012 6:17 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 25, 2014 2:50 PM
Points: 550, Visits: 1,611
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)

Post #1349983
Posted Friday, August 24, 2012 10:10 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 28, 2014 11:19 AM
Points: 990, Visits: 2,219
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));


Post #1349997
Posted Friday, August 24, 2012 11:31 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
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
Post #1350005
Posted Saturday, August 25, 2012 12:12 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 8:19 AM
Points: 50, Visits: 154
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.
Post #1350054
Posted Sunday, August 26, 2012 12:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
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
Post #1350092
Posted Thursday, August 30, 2012 11:00 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 25, 2014 2:50 PM
Points: 550, Visits: 1,611
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'

Post #1352373
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse