Post reply

Search and Replace

  • April 24, 2010 at 8:21 am

    #96657

    I have a string of data

    'Program SITE Location'

    'Program Division SITE Area Location'

    I need to replace SITE with SITE1 but the Text next to it must be in the exact same position so if Location starts on position 30 I have to remove one of the spaces between the SITE and Location

    'Program SITE Location'

    'Program SITE1 Location' and not become

    'Program SITE1 Location'

    I can use the replace command and charindex to find the SITE.

    How to reduce the additional space aswell.

    Thanks

  • April 24, 2010 at 12:31 pm

    #1157306

    why can not u replace SITE[space] with SITE1

  • April 24, 2010 at 1:57 pm

    #1157333

    Whoops

    [

    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 24, 2010 at 5:42 pm

    #1157355

    The replace command is adding the additional space the later text must be in the same position. If it starts on position 30 and I add an additional column of text it will be position 31.

  • April 24, 2010 at 8:47 pm

    #1157366

    DECLARE @L VARCHAR(10)

    SET @L = 'Location'

    SELECT id, S, CHARINDEX(@L,S) AS 'LocA', REPLACE(S,'SITE ','SITE1') aS 'Modified'

    ,CHARINDEX(@L,REPLACE(S,'SITE ','SITE1')) AS 'LocB'

    --== notice blank (space) ^

    FROM #Table

    DROP TABLE #Table

    /* Result:

    idS LocA Modified LocB

    1 Program SITE Location 14 Program SITE1Location 14

    2 Division SITE Area Location 20 Division SITE1Area Location 20

    3 Program SITE Location 15 Program SITE1 Location 15

    4 Program SITE Area Location 20 Program SITE1 Area Location 20

    LocA and LocB are the character position for the starting place of the word "Location", it has NOT changed

    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 25, 2010 at 8:20 am

    #1157388

    Thanks I will look at the script. The Location may not be this word if maybe just Area without Location ..basically I would not know what is after SITE blank blank (new text word).

    Definately gives me something to work with.

  • April 25, 2010 at 9:33 am

    #1157400

    TRACEY-320982

    --==Extracted segments only for Illustration purposes

    DECLARE @L VARCHAR(10)

    SET @L = 'Location'

    CHARINDEX(@L,S) AS 'LocA',

    ,CHARINDEX(@L,REPLACE(S,'SITE ','SITE1')) AS 'LocB'

    Please note. The parameter @L is only used to determine the position of the word "Location". To illustrate that the REPLACE(S,'SITE ','SITE1') function performs as you required, that is, the position of the beginning of the next word, what ever it maybe, has not been altered. And it is not needed and should not be used in your final T-SQL

    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 25, 2010 at 9:54 am

    #1157403

    Thanks for the clarification.

  • April 26, 2010 at 2:28 pm

    #1157887

    You can also try STUFF function. Here is an example:

    DECLARE @Tbl TABLE (Txt varchar(50));

    INSERT INTO @Tbl(Txt)

    SELECT 'Program SITE Location' UNION ALL

    SELECT 'Program Division SITE Area Location'

    SELECT

    OldTxt = Txt,

    NewTxt = STUFF(Txt, CHARINDEX('SITE ', Txt), 5, 'SITE1')

    FROM @Tbl

    WHERE CHARINDEX('SITE ', Txt) > 0

    --Vadim.

    --Vadim R.

  • April 27, 2010 at 12:30 am

    #1157995

    I would like to suggest you about the site name just replace it as site1..

    Resume Writing

Viewing 10 posts - 1 through 9 (of 9 total)

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