cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MG_Metro
Regular Visitor

Remove Special Character from returned SQL data field

when I got my data fetched from SQL db, one of the field contains some special character and ruining my automation, I try to filter it, but the append to string is not excepting the expiration I'm putting there, Idk how to do this, please advice.

 

MG_Metro_0-1618853926135.png

 

this field contains invalid character, and I had used the expression as below

 

MG_Metro_1-1618854018713.png

and the expression is replace(items('Apply_to_each_3')?['value']?['ParameterValue'],'"','') 

 

but it's giving me error, please help, thanks. 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @MG_Metro 

 

quite simply item()?['value']?['ParameterValue'] doesn’t exist. Are you able to look at the history output for the fetch sql action and copy the json here? It will help construct an expression for the value you are looking for.


last guess:

item()?['ParameterValue']

then a copy of your json and I’ll try to help.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here

 

View solution in original post

8 REPLIES 8
DamoBird365
Super User
Super User

Hi @MG_Metro 

 

What's the error?

 

Could the correct expression be replace(item()?['value']?['ParameterValue'],'"','')

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here

MG_Metro
Regular Visitor

Oh, sorry forgot to include the error, here is it

 

MG_Metro_0-1618865478668.png

 

@MG_Metro 

 

Your screenshot suggests that your original expression evaluates to null. Can you try the amended expression I supplied above?

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here

tried to update it, but still same error, thx

Hi @MG_Metro 

 

quite simply item()?['value']?['ParameterValue'] doesn’t exist. Are you able to look at the history output for the fetch sql action and copy the json here? It will help construct an expression for the value you are looking for.


last guess:

item()?['ParameterValue']

then a copy of your json and I’ll try to help.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here

 

View solution in original post

as you seen on first image, if I just put the SQL item, it works, but the double quote on the table value is breaking the script. 

 

MG_Metro_0-1618866211453.png

 

If you hover your mouse over the dynamic value ParameterValue, you will see the expression and then you can copy this into the replace expression. The other way to see the expression is to click on the ellipses of the append to variable action and inspect code. This will help you see the correct expression for ParameterValue.

MG_Metro
Regular Visitor

oh, thx for that info, I see, really useful, looks like the last expression works, let me try with the problematic one, and update you soon. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (3,618)