SQL Server Performance

Stored Procedure to remove commas

Discussion in 'General Developer Questions' started by starwarsbigbang, Oct 19, 2006.

  1. starwarsbigbang New Member

    Hi,

    I am looking forward to write a stored procedure that will replace all the commas (,) in a column for a given table. e.g., I have a table that has a column called sports with values say 'cricket,badminton,volleyball,baseball,skiing'

    There are several rows and that particular column has values like these. I want to remove these commas and the value should look like say ''cricketbadmintonvolleyballbaseballskiing' i.e., I want to eliminate the commas.

    Can anyone help me out.




    Thanks,

    StarWarsBigBang
  2. chiragkhabaria New Member

    There is a replace function in sql server

    Select replace('cricket,badminton,volleyball,baseball,skiing',',','')

    Chirag

    http://chirikworld.blogspot.com/
  3. FrankKalis Moderator

  4. starwarsbigbang New Member

    Thanks Frank and Chirag,

    I am aware of the replace function. Using your above method, I will have to manually update the values. The table has several rows with commas in the sports column. Any idea how these can be done through stored procedures.



    Thanks,

    StarWarsBigBang
  5. chiragkhabaria New Member

    Update TAbleName Set SportColumn = Replace(SportColumn,',','') ??

    Chirag

    http://chirikworld.blogspot.com/
  6. FrankKalis Moderator


    UPDATE your_table SET your_column = REPLACE(your_column, ',', '')

    That's just a single statement taking care of all rows in your_table.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  7. Madhivanan Moderator

    Frank, Chirag seems faster [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />StarWarsBigBang, Learn SQL<br /<a target="_blank" href=http://www.sql-tutorial.net/>http://www.sql-tutorial.net/</a> <br /<a target="_blank" href=http://www.firstsql.com/tutor.htm>http://www.firstsql.com/tutor.htm</a> <br /<a target="_blank" href=http://www.w3schools.com/sql/default.asp>http://www.w3schools.com/sql/default.asp</a> <br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  8. DilliGrg Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />Frank, Chirag seems faster [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />StarWarsBigBang, Learn SQL<br /<a target="_blank" href=http://www.sql-tutorial.net/>http://www.sql-tutorial.net/</a> <br /<a target="_blank" href=http://www.firstsql.com/tutor.htm>http://www.firstsql.com/tutor.htm</a> <br /<a target="_blank" href=http://www.w3schools.com/sql/default.asp>http://www.w3schools.com/sql/default.asp</a> <br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />Great, Thanks for the link.
  9. Madhivanan Moderator

    >>Great, Thanks for the link.

    You are welcome

    Madhivanan

    Failing to plan is Planning to fail
  10. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />Frank, Chirag seems faster [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes, this time...[8D]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page