Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Check if string contains certain characters

Hey all,

 

I am looking for a solution to check wether an input value contains any characters that are not allowed. The system allows a to z, 0 to 9, - and _. I would think that checking if a character is in this set is easer than checking wether the character is out of the set.

 

So far the solution I came up with is to split up the string into separate characters and then check per character if it is part of the set by checking IF(OR(OR(OR(CONTAINS("character","a"), CONTAINS("character","b"),.....)))

 

However I would suppose that there can be an easier and less computative extensive method to solve this problem. Does anyone have an idea how to solve this problem in concise way?

 

Expected output: TRUE if a string only contains the characters a to z, 0 to 9, - and _ and FALSE it it contains a character that is not part of the mentioned set.

Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKNjQyjleK1YlWqqis0o23BDOBwjGlBgZGZnEQqaTklHgVpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TextColumn = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TextColumn", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Length([TextColumn]) = Text.Length(Text.Select([TextColumn], {"a".."z", "0".."9", "-", "_"})) then "Y" else "N")
in
    #"Added Custom"

     

    Pat

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKNjQyjleK1YlWqqis0o23BDOBwjGlBgZGZnEQqaTklHgVpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TextColumn = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TextColumn", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Length([TextColumn]) = Text.Length(Text.Select([TextColumn], {"a".."z", "0".."9", "-", "_"})) then "Y" else "N")
in
    #"Added Custom"

     

    Pat