How to replace commas in returned value from sql query before binding to control

I have a query from one DB that is used to populate a listbox.  When the values are bound to the listbox for each value that has a , in it I want to replace it with a ;

This because my listbox is a multi select and if I don't remove those commas on the list coming in things are going to get very confused.
Who is Participating?
HainKurtConnect With a Mentor Sr. System AnalystCommented:
do this after data binding... again, if you have control over sql, use sql solution

select id, name from mytable
select id, replace(name,',',';') as name from mytable
Paul MacDonaldDirector, Information SystemsCommented:
If you need to do the replacement in SQL, we might need to know about the back end.  

If you're doing the replacement in VB.NEt you should just be able to
     Replace(value, ",", ";")
HainKurtSr. System AnalystCommented:
similar thing if you iuse it in sql

select replace('Hain,Kurt',',',';')
--> Hain;Kurt
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

HainKurtSr. System AnalystCommented:
how do you bind it to listbox? if you have something like this

select id, name from mytable

and bind it to selectbox, use id for value, and name for Text

then when you get it back, you will end up with numbers like '1,3,5,8' instead of text... do not use text columns for values...
thamilto0410Author Commented:
HainKurt and paulmacd,
I have an oracle backend and when I tried using select replace the codebehind objected.

I thought I could make the change in I am filling a dataset from an oralcedataadapter and binding to the listbox in codebehind DataValueField, DataValueText and Databind.

When I tried the replace on the datavaluefield ie:  

files_processed.DataValueField = replace("thecolumnfromsql", ",", ";")

That also did not work.  What to do next?
HainKurtSr. System AnalystCommented:
you cannot do it in codebehind liek that

either use sql (update your select statement - i prefer this one, easy)
in a loop change all item text

for each i as ListItem in dd.Items
Paul MacDonaldDirector, Information SystemsCommented:
[HainKurt] is right:  your best bet is to do the replace in the SQL.  You can do the replace on the individual list items, but that's slow and messy.
thamilto0410Author Commented:
Thank you to hainkurt and paulmacd.  I figured out the way to do it in the sql and it is working great.  All you experts rocks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.