Which MS Access Function - 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

Other Forums

MS Access

Which MS Access Function

Author

Topic

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2009-06-26 : 15:47:38

Hey guys,

Consider the field and value below:

FULL_DESC
-----------------------------------
BALA-DEFAULT PA 06

I need to remove '06' wherever it exists in the FULL_DESC field for every record in a table. What is the best way of doing this? I tried using the REPLACE function in the SELECT clause but this query returns a NULL value.

SELECT REPLACE (FULL_DESC, '06', '', LEN(FULL_DESC) - 2, 2) ...

Here are the parameters broken down:

REPLACE(String, Find, Replacement, [Start], [Count])

String: FULL_DESC field
Find: '06'
Replace: ''
Start: We need to start 2 characters counting from right-to-left. That is why I used the expression LEN(FULL_DESC) - 2
Count: Not sure about this value. I used 2, so that it replaces the 2 characters '06'

If I am nesting these functions incorrectly please give me a better alternative.

Thank you!
John

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-26 : 18:44:15

If you want to replace EVERY occurence of '06' in that column
just use SELECT REPLACE(FULL_DESC, '06', '')

No, you're never too old to Yak'n'Roll if you're too young to die.