Error in function argument - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error in function argument

Author

Topic

erico
Starting Member

34 Posts

Posted - 2007-04-17 : 17:44:57

I have a table with over 11,000 records and I need to do a find and replace using SET and Where conditions. Basically I have one column in the table called RealAudioLink. It contains entries like: wkdy20070416-a.rm and wkdy20070416-b.rm and conv20070416.rm.

I need the select statement to find all wkdy entries and replace those characters with Weekday. I also need it to find all dashes and small a's and b's and replace with null or nothing. Then I need it to insert a capital letter A or B in the
wkdy20070416-a.rm filename so that when it's all said and done that entry would read:

WeekdayA20070416.rm
WeekdayB20070416.rm
Conversation20070416.rm

Here is the code I am working with. It needs help. I'm close but I'm not knowledgeable with using SET or with removing dashes and inserting capital letters all in the same select statement.


UPDATE T_Programs_TestCopy
(SET  RealAudioLink = REPLACE(RealAudioLink, '-a', '')
AND
(SET RealAudioLink = REPLACE(RealAudioLink, 'wkdy', 'WeekdayA')
WHERE (RealAudioLink LIKE 'wkdy%'))

I've never done anything like this before so I would be very appreciative of any assistance with the select statement. I am reading up on it but it would be great to get another perspective from a more experienced sql developer.

Thanks

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-17 : 18:12:39

[code]
UPDATE T_Programs_TestCopy
SET RealAudioLink = Replace(Replace(REPLACE(RealAudioLink, '-a', ''),'-b',''),'wkdy','WeekDayA')
WHERE RealAudioLink LIKE 'wkdy%' or
RealAudioLink LIKE '%-a%' or
RealAudioLink LIKE '%-b%'
[/code]

erico
Starting Member

34 Posts

Posted - 2007-04-17 : 18:16:56

Vinnie -

When I tried your suggestion I got an eror from within the query editor:

String or binary data would be truncated

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-17 : 18:20:12

What type of column is RealAudioLink?

I am able to use it w/o issue on my test tables. It appears that by changing wkdy to weekdayA it is exceeding the column width.

Try adjusting the column width to larger.

erico
Starting Member

34 Posts

Posted - 2007-04-17 : 18:27:56

It was set to varchar 100, I just changed it to 200. Now I'll test it.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-17 : 18:34:10

100 Charectors should've been plenty if all the filenames are similiar to the ones you mentioned. Just to make sure let's do some additional filtering

UPDATE T_Programs_TestCopy
SET RealAudioLink = Replace(Replace(REPLACE(RealAudioLink, '-a.rm', '.rm'),'-b.rm','.rm'),'wkdy','WeekDayA')
WHERE RealAudioLink LIKE 'wkdy%' or
RealAudioLink LIKE '%-a.rm' or
RealAudioLink LIKE '%-b.rm'

erico
Starting Member

34 Posts

Posted - 2007-04-17 : 18:35:50

Tested good, Thank you so much. You have no idea how much appreciate you helping me with the code. Most developers won't do that these days. You rock!

If you want I'll be happy to email you a free digital photo from my collection. It would be 1024 x 768 ish and completely free. Just let me know what you prefer. Waterfalls, Landscapes, Wildlife etc. In my off time I do Nature Photography and have been shooting film and digital for many years.

It's the least I could do for your help. Send your requests to eric underscore letter o photographer at yahoo dot com.

Thank You again!

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-17 : 18:36:57

Glad I could help, no need for the photo.

erico
Starting Member

34 Posts

Posted - 2007-04-17 : 18:44:02

OK just checked the table visually and notice a small problem still exists. The RealAudioLink says WeekDayA for both entries on April 17th, but the MP3Link says weekdaya and weekday b, so something isn’t quite right yet.

I can I assure that I dont get redundant letter A's?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-17 : 18:57:36

I see what your saying.
The easiest way is to use two queries. Make sure you are not running this on the corrupted data set, and that you are dealing with a fresh copy.


UPDATE T_Programs_TestCopy
SET RealAudioLink = Replace(REPLACE(RealAudioLink, '-a.rm','.rm'),'wkdy','WeekDayA')
WHERE RealAudioLink LIKE 'wkdy%' and
RealAudioLink LIKE '%-a.rm' 

UPDATE T_Programs_TestCopy
SET RealAudioLink = Replace(REPLACE(RealAudioLink, '-b.rm','.rm'),'wkdy','WeekDayB')
WHERE RealAudioLink LIKE 'wkdy%' and
RealAudioLink LIKE '%-b.rm' 

--Here's it in 1 query
UPDATE T_Programs_TestCopy
SET RealAudioLink = case 
					when charindex(RealAudioLink,'-a.rm') > 0 then Replace(REPLACE(RealAudioLink, '-a.rm','.rm'),'wkdy','WeekDayA')
					when charindex(RealAudioLink,'-b.rm') > 0 then Replace(REPLACE(RealAudioLink, '-b.rm','.rm'),'wkdy','WeekDayB')
					else RealAudioLink
					End
WHERE RealAudioLink LIKE 'wkdy%' and
(RealAudioLink LIKE '%-b.rm' or RealAudioLink LIKE '%-a.rm')

erico
Starting Member

34 Posts

Posted - 2007-04-17 : 19:12:31

Yeah I see what your saying. I'll try it again using the selects separately like you mentioned. I might not reply again until tomorrow. It's 4:15pm PST and I'm going home.

Thanks

erico
Starting Member

34 Posts

Posted - 2007-04-18 : 11:09:03

Vinnie -

Ok Got it to work. Now the two updates are done. Very cool! I'm wanting to tweak your code so that I can now run another select to change the letters conv to read conversation. So would I do something like this:


ORIGINAL CODE

UPDATE T_Programs_TestCopy
SET RealAudioLink = Replace(REPLACE(RealAudioLink, '-b.rm','.rm'),'wkdy','WeekDayB')
WHERE RealAudioLink LIKE 'wkdy%' and
RealAudioLink LIKE '%-b.rm'


MY VERSION

UPDATE T_Programs_TestCopy
SET RealAudioLink = Replace(REPLACE(RealAudioLink, '',''),'conv','conversation')
WHERE RealAudioLink LIKE 'conv%' and
RealAudioLink LIKE '%' ( not sure about this part )

I've got most of it but I'm not confident I've put enough changes in this to make it work. Your thoughts?

erico
Starting Member

34 Posts

Posted - 2007-04-18 : 11:58:56

Got it!

UPDATE EricsCopy
SET RealAudioLink = Replace(REPLACE(RealAudioLink, '.rm','.rm'),'conv','conversation')
WHERE RealAudioLink LIKE 'conv%' and
RealAudioLink LIKE '%.rm'

This fixed the conv references and changed them to read conversation. I knew if I kept at it I'd get it. Of course it helps to have had help from Vinnie yesterday. :-)

Thanks again to Vinnie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources