Remove multiple occurrences of text from a string

[btappan]

I have a linked table from an accounting program that has data in a single field like below:



[Location]
BLDG:1-2
BLDG:1-5, BLDG:1-16
BLDG:1-2
BLDG:1-2, BLDG:1-5, BLDG:1-119

I want my select query to remove all of the BLDG: so it looks like this:

1-2
1-5, 1-16
1-2
1-2, 1-5, 1-19

Can this be done within my select query?

[rpeare]

did you try replace([location], "BLDG:", "")

[btappan]

Just tried it, and for some reason it does not remove them. it does give "error" on null fields, which I can work around.

[btappan]

strange, it didn't like the : in there, i removed it from the replace string, and it then worked, put it back in, and it continued to work.

[rpeare]

it worked for me to handle nulls you'd just do


iif(isnull([location]), null, replace([location], "BLDG:", "")

assuming you're trying to do this in a query.


note this will not work if you have a space between your BLDG and your colon the text string has to match exactly what you're trying to remove.

[btappan]

wound up using

location: IIf(Nz([Qty_On_Hand].[location],"")="","",Replace([Qty_On_Hand].[location],"BLDG:",""))