Post reply

Derived column

  • 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!!

  • August 24, 2012 at 12:11 pm

    #1529898

    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!

  • August 24, 2012 at 6:17 pm

    #1530009

    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)

  • August 24, 2012 at 10:10 pm

    #1530021

    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.

  • August 24, 2012 at 11:31 pm

    #1530026

    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:

  • August 25, 2012 at 12:12 pm

    #1530066

    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.

  • August 26, 2012 at 12:03 am

    #1530097

    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:

  • August 30, 2012 at 11:00 am

    #1532023

    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'

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

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