Post reply

T-SQL query

  • April 17, 2011 at 6:12 pm

    #389305

    Experts..

    Table 1 looks like this:

    Col1 Col3

    String1 String1.String2

    .....

    I want to transform the table so it would look like this:

    Col Col2

    String1 String2

    ....

    Where String1 , String2 are of varchar type

    So basically I need to work on Column 2. Chop of the characters that are present in Column1

    This is done on SQL server 2005 express.

    Thanks for the help!

  • April 17, 2011 at 6:25 pm

    #1312573

    To help those who are willing to help you, please post table definition,sample data and expected results, using the outline/method which you will be able to read and refer to by clicking on the first link in my signature block.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • April 17, 2011 at 7:10 pm

    #1312575

    CREATE TABLE [dbo].[BldgA](

    [CX] [nvarchar](255) NULL,

    [Point] [nvarchar](255) NULL,

    [Link] [nvarchar](255) NULL

    )

    sample data:

    BldgA G1.EngCoolTemp G1.EngCoolTemp.C1.GenLoCoolT

    I want to change it to look like this:

    BldgA G1.EngCoolTemp C1.GenLoCoolT

    Script for the data:

    INSERT INTO [database1].[dbo].[BldgA]

    ([CX]

    ,[Point]

    ,[Link]

    )

    VALUES

    ('BldgA'

    ,'G1.EngCoolTemp'

    ,'G1.EngCoolTemp.C1.GenLoCoolT'

    )

  • April 17, 2011 at 8:36 pm

    #1312578

    Consider this, though you would need to confirm that the @Val1 is in fact the prefix of @Val2:

    declare@Val1varchar(50)

    , @Val2varchar(50)

    select@Val1 = 'G1.EngCoolTemp'

    , @Val2 = 'G1.EngCoolTemp.C1.GenLoCoolT'

    selectsubstring(@Val2, len(@Val1)+2, 50)

    Steve.

  • April 18, 2011 at 9:08 am

    #1312868

    This is what I did:

    update BldgA

    set Link1 = substring(Link1,len(point)+2, len(link1))

    It worked.

    Thanks

  • April 18, 2011 at 2:47 pm

    #1313090

    I'm not 100% sure of the requirement. If the goal is just to remove the string in the column Point from the Link column or if there is more too it than that. At any rate, here is another way to remove the string fragment:UPDATE dbo.BldgA

    SET Link = REPLACE(Link, Point + '.', '')

  • April 18, 2011 at 2:50 pm

    #1313091

    Thanks

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

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