Archive
T-SQL Strip Invalid Characters
Occasionally I need to strip invalid characters from a string and the requirements are often subtly different, sometimes we want numbers only, other times numbers and decimal points, and maybe only letters.
We’ve probably all had to do this at some point and probably grabbed something off the internet, but I finally took the time to write a more generic and performant version as listed below which allows for a valid character pattern to be passed in.
Create Function dbo.StripInvalidCharacters
/******************************************************************************
Description:
Strips invalid characters from a string. This is useful for cleaning up
strings such as telephone numbers or human entered values such as
'10.7 miles'
In:
@input
The string to strip characters from.
@valid_character_pattern
A search pattern that identifies invalid characters. Some example are:
- '0-9' - numbers excluding decimal point
- '0-9.' - numbers including decimal point
- 'a-z' - letters only
- 'a-f0-9' - hexadecimal only
History:
10-May-2016 S.Wilber
Created
******************************************************************************/
(
@input varchar(1000)
,@valid_character_pattern varchar(100)
)
Returns varchar(1000)
As
Begin
While PatIndex('%[^' + @valid_character_pattern + ']%', @input) > 0
Set @input = Stuff(@input,PatIndex('%[^' + @valid_character_pattern + ']%', @input), 1, '')
-- return
Return @input
End
Feel free to copy and re-use. If you spot any improvements, please let me know and I’ll update this post.
Loading JSON List of Lists into PowerBI using PowerQuery
I was recently stuck trying to load a JSON source into PowerBI as the source itself appeared to be at its core a list of lists as per the below example.
{
"error":[],
"result":{
"XETHZUSD":[
[
1438905600,
"3.00000",
"3.00001",
"3.00000",
"3.00000",
"3.00000",
"123.93056831",
4
],
[
1438992000,
"3.00000",
"3.00000",
"0.15000",
"1.19999",
"0.59918",
"1942.88814725",
20
],
[
1439164800,
"1.19999",
"1.19999",
"1.19999",
"1.19999",
"0.00000",
"0.00000000",
0
],
[
1439251200,
"0.79969",
"1.15000",
"0.65038",
"0.99000",
"0.77715",
"9234.56870480",
84
],
[
1439337600,
"1.00000",
"1.28800",
"0.90500",
"1.28800",
"1.08120",
"1604.20230300",
44
]
],
"last":1462752000
}
}
This is actually an extract of the result from the following public API call to Kraken. https://api.kraken.com/0/public/OHLC?pair=XETHZUSD&interval=1440
When trying to load this as a Web source it was initially presented as expected as shown below.
Drilling down on the ‘result’ Record produced the following where XETHZUSD was presented as a list. No problem so far.
Drilling down further on the XETHZUSD list produced the following, a list of lists.
There is not much that can be done at this point without transforming the list to a table, however this is only presented the list of lists as a column of lists as follows.
Expanding the columns does not create a result that we would be looking for as all the content from each individual list is merely expanded into the same list as shown below.
The secret is to add columns that reference specific items within the List stored in ‘Column1’.
To do this I added a new column with the following formula:
List.First([Column1])
This statement returns the first item in the list stored in ‘Column1’.
This is great – it allows us to create our first column by dipping into the list. However, there is not a direct way to access individual elements of a list given their position.
Therefore to get the second item we need to skip over the first item. Thus, the formula for the second column is as follows:
List.First(List.Skip([Column1],1))
This can then be repeated as required, e.g.
List.First(List.Skip([Column1],1))
List.First(List.Skip([Column1],2))
List.First(List.Skip([Column1],3))
etc.
Once you have all the elements of the list required in their own separate columns the original list column, ‘Column1’ in our example here, can be removed. The final result is as shown below.
WilberBeast 