First, you have to specify whether you want to remove characters from the beginning ('leading'), the end ('trailing'), or both ('both', as used above). RIGHT(date, LENGTH(date) - 11) AS cleaned_time Since we know that the first 10 characters will be the date, and they will be followed by a space (total 11 characters), we could represent the RIGHT function like this: SELECT incidnt_num, So LENGTH(date) will always return 28 in this dataset. The LENGTH function returns the length of a string. If it wasn't consistent, it's still possible to pull a string from the right side in a way that makes sense. RIGHT works well in this case because we know that the number of characters will be consistent across the entire date field. RIGHT does the same thing, but from the right side: SELECT incidnt_num, The following query pulls out only the ogimage: '/images/og-images/og-default.png' The syntax is LEFT(string, number of characters).Īs a practical example, we can see that the date field in this dataset begins with a 10-digit date, and include the timestamp to the right of it. You can use LEFT to pull a certain number of characters from the left side of a string and present them as a separate string.

LEFT, RIGHT, and TRIM are all used to select only certain elements of strings, but using them to select elements of a number or date will treat them as strings for the purpose of the function. However, using a particular function will, in many cases, change the data to the appropriate type. Most of the functions presented in this lesson are specific to certain data types.

The two coordinates are also broken out into the lat and lon fields, respectively. Some field definitions: location is the GPS location of the incident, listed in decimal degrees, latitude first, longitude second. There is one row for each incident reported. It was collected from the SF Data website on February 16, 2014. This lesson features data on San Francisco Crime Incidents for the 3-month period beginning Novemand ending January 31, 2014. Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis.

Teradata convert string to number