| Author |
Topic |
|
boris37
Starting Member
12 Posts |
 Posted - 2009-03-05 : 16:38:51
|
| So i have to write a query for my work and i'm totally lost using sql server.Here is what the query is supposed to do:1) Remove a set bunch of chars from a string(two different rows, same string...)2) Keep the original string while taking out these original chars.3) I need an Array and a loop to be able to do itI am trying write a block but every time i do anything sql server gives me problems saying "didn't expect this here" or something.Is this even doable in sql server? I kinda had something like :DECLARE CURSOR c1 is ('-','/');BEGIN loop ENDin my mind but sql server even gives me issues with the cursor... any tips would be nice but i just need an array i can manually populate. I can run the following query but i have to do it 10-15 times to remove the chars and i want something cleaner(even if it takes me much longer)UPDATE coalarm set almsentence=replace(almsentence, '-', ' of '), normsentence=replace(normsentence, '-', ' of ') where almstorenid=23127**Note** this is only one row hence the "where" clause. THanks!Lastly this is my first time using sql server and it's a little different from sqlplus... at least the cryptic errors and fact i can't do anything i could in sqlplus but maybe it's just me. |
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-03-05 : 16:41:34
|
| A cursor is most likely not needed in this case. Post us some sample data and expected results and we'll see what we can do.Some days you're the dog, and some days you're the fire hydrant. |
 |
|
|
boris37
Starting Member
12 Posts |
Posted - 2009-03-05 : 16:47:01
|
| 3-26-60-2W5M Rustler flow alarmThat's the string. I just need something to run a loop and remove the -'s and replace with ' of ' and remove W5M completely. Although there are lots more that's just one line. I also need to remove slashes, w5m's, w4m's, double spaces etc.Is this even possible? I just started writing again in sql server and all my old oracle queries don't function properly if at all.Mainly i just don't know how to do a loop or a VARRAY so i can add something to remove all the w5m's etc without writing 10+ queries which i could do... but that's so amatuer |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-03-05 : 17:03:28
|
| Can you may be include everythin in a single REPLACE statement...like this...SELECT REPLACE(REPLACE(REPLACE('3-26-60-2W5M Rustler flow alarm','-','of'),'W5M',''),' ','') |
|
|
|
boris37
Starting Member
12 Posts |
Posted - 2009-03-05 : 17:05:44
|
| i could, does that work the same cause it's very doable...Sorry your answer looks like a question not sure how to reply. I will try though. I just figured it as faster to write a loop with an array then make an ugly embedded replace. |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-03-05 : 17:43:04
|
| Yeah..that ofcourse works...but if you have like 10 or 12 strings to be replaced...that statement could get ugly..but will work nevertheless and its quick as well. |
|
|
|
boris37
Starting Member
12 Posts |
Posted - 2009-03-05 : 18:00:18
|
| hmm yeah i kind of have 9 maybe 10. Any other ideas for something a little cleaner. Time is not an issue i really have nothing else to work on so i want to make it look as nice as possible and 9 replaces will just be seriously ugly haha.Oh Edit. I have to replace - / \ all with OF while the rest:W1MW2MW3M.etcAll are being cut out, so even 2 simple loops maybe? would work. No idea at this point i'm shooting blanks. I mean if we could set an variable array with name being the second arguement in the replace and the contents being the third then it would have what i want beautifully. I am just not sure on how to do it in sql but i could manage in C# or something so i'm a little flustered. |
|
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-03-05 : 18:45:48
|
| This may not be the most efficient approach, but I would just clean the data one step at a time. Separate out the steps and create separate scripts for each one. For example, write the script to replace W1M, M2M and W3M with '', then do a separate script to get rid of all the double spaces, etc. Like I said, it's not as efficient as a single statement but it can save you some sanity instead of trying to write it all at once.Some days you're the dog, and some days you're the fire hydrant. |
|
|
|
|
Writing a query in server 2005