URIUnpack Example - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
8.10
1.1
Published
October 2019
Language
English (United States)
Last Update
2019-12-31
dita:mapPath
ima1540829771750.ditamap
dita:ditavalPath
jsj1481748799576.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
Teradata Vantageā„¢

Input

The input table, uris_input, has five URIs, some of which include percent-encoded characters.

uris_input
id uri_column
1 'https://www.sample.com/webhp?p1=chrome&ph=hello+world&p3=UTF-8#fragment1'
2 'http://www.ietf.org/rfc/rfc2396.txt'
3 'ldap://[2001:db8::7]/c=GB?objectClass?one'
4 'telnet://192.0.2.16:80/'
5 'http://www.bar.com/baz/foo?p1=netscape&p2=%7bhello+world%7d&p3=UTF#This+%2Bis+%2Bfragment+%2Btoo'

SQL Call

SELECT * FROM URIUnpack (
  ON uris_input
  USING
  URIColumn ('uri_column')
  Queries ('p1','p2','p3')
  OutputType ('SCHEME','HOST','PATH','FRAGMENT')
  Accumulate ('id')
  PrintNullQueries ('true')
) AS dt ORDER BY id;

Output

The characters encoded in the input table as %20, %7b, %7d, and %2B are decoded in the output table as the space character, left brace ({), right brace (}), and plus sign (+), respectively. When a URI does not have a specified parameter, the value of that parameter is NULL.

 id scheme host           path             p1       p2            p3    fragment             
 -- ------ -------------- ---------------- -------- ------------- ----- -------------------- 
  1 https  www.google.com /webhp           chrome   hello world   utf-8 fragment1           
  2 http   www.ietf.org   /rfc/rfc2396.txt null     null          null  null                
  3 ldap   [2001:db8::7]  /c=gb            null     null          null  null                
  4 telnet 192.0.2.16     /                null     null          null  null                
  5 http   www.bar.com    /baz/foo         netscape {hello world} utf   this+is+fragment+too

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.