课程: SQL Essential Training

Connecting strings - SQL教程

课程: SQL Essential Training

开始一个月试用

Connecting strings

“

- [Instructor] Now, to demonstrate the use of string functions, we can take a look at the following scenario. Now, let's say our managerial team at WSDA Music is interested in sending out personalized postcards to each one of their US-based customers. Now, to do this, we would need a single field that comprises of the customer's first name, last name, address and that would be their full address which would include the street, the city, the state, and the zip code. Let's see how we could use our SQL string functions to build this field. Now, when it comes to joining separate fields, this is referred to as concatenating them in SQL. And to achieve this, we use the double pipe operator in SQLite. I'm going to make a quick single line comment just to demonstrate that the double pipe is just simply the pipe symbol on your keyboard two times. Okay, now let's start to build our new field which is going to be our mailing address. Let's put a new comma in our SELECT clause and then start building our new field. First, we need the first name, so I'm going to put FirstName, then I'm going to use the double pipe operator like this, then I'm going to put the LastName. So by doing this, we'll effectively join two separate fields together. Let's now observe the result here. Okay, we now have a new field and this field has the first and last name fields in one single field. The only issue is we don't have it looking very readable without a space between the first and the last name. So let's go ahead and modify our current statement. To do this, we would need to now put a pair of single quotes with a space in between. We want to make sure and have that space in between. And then we're going to put another pair of double pipe and make sure that LastName is there. And now let's rerun our statement. What we've done is created a space between the first and last name and that looks a lot more readable to the human eye. Okay, let's continue building out our mailing address. We have first and last name with a space in between. We do need the street address. So let's follow this with address, which we'll start with our double pipe. We put a space again because we want a space after that last name. And then we put another pair of double pipes and we put our address. Now let's rerun our statement and slowly we're building out. We have name, which is first and last, plus the address. This time, we're going to put a pair of double pipes. And then between the double pipes, we want not just a space, but we want also a comma there. So we want it formatted slightly different by putting a comma in between. And now let's put city and let's run our query. Okay, now, if we extend our result here, we do see we have the street, followed by a comma then the city. Let's continue and then put the state. So by now, we're just repeating the pattern. No comma this time. And we put state. And finally, let's put the zip code. Repeat the pattern once more. And the zip code is referred to as the postal code in our customer table. Now let's run our full concatenated address. And we do have a complete address that looks nicely formatted. First and last name, followed by street, city, state, and zip. Last but not least, let's give our new column a nice title and call it mailing address. One more time, we will rerun this. And here again, we have now a nicely formatted address, complete with a nice name that we have aliased as mailing address. Well done. And in this way, we have done something very significant and a very common action with regard to manipulating data in an existing database via string functions.

内容