LEFT, RIGHT, CHARINDEX and SUBSTRING functions in sql server Part 23 — Transcript

Full transcript

1. 0:00Hello, welcome to Premium Technologies.
2. 0:01I am Wenut. This is part 23 of SQL
3. 0:04Server. In this session, we'll talk
4. 0:07about a few more commonly used built-in
5. 0:10string functions available in SQL
6. 0:12Server. Specifically, we'll talk about
7. 0:14left, right, car index, and substring
8. 0:17functions. And we'll also look at a
9. 0:19realtime example of using these string
10. 0:21functions. Now, before continuing with
11. 0:23this session, I strongly recommend to
12. 0:25watch parts 11 and 22 of this video
13. 0:28series.
14. 0:31Okay. So what is left function? Left
15. 0:34function returns the specified number of
16. 0:37characters from the left hand side of
17. 0:39the given character expression. Let's
18. 0:41understand what we mean by this. So
19. 0:44select let's say I have a string called
20. 0:47abcde EF.
21. 0:52Okay. Now what I want to do is I want to
22. 0:55retrieve the first three characters from
23. 0:58the left hand side of the string. Okay.
24. 1:01So I can use the left function for that.
25. 1:04Okay. So select left and if you look at
26. 1:08the intellisense that is available in
27. 1:09SQL server it is expecting um an
28. 1:13expression of type and care. So let's
29. 1:15pass this there.
30. 1:18Okay. And then the next parameter is the
31. 1:21number of characters that you want from
32. 1:23the left hand side of this expression.
33. 1:25Let's say I want three characters. So
34. 1:27when we try that, we should get ABC. On
35. 1:30the other hand, if you want maybe four
36. 1:32characters, just pass in four. You
37. 1:34should get also I mean get D as well.
38. 1:37Okay. Now, so right is the inverse of
39. 1:42left. Okay. Left function returns the
40. 1:45specified number of characters from the
41. 1:46left hand side of the given character
42. 1:48expression. Whereas right returns the
43. 1:50specified number of characters from the
44. 1:52right hand side of the given character
45. 1:54expression. Okay. So if you want let's
46. 1:56say def okay then all you do is instead
47. 2:01of left you will say right and from the
48. 2:04right side let's say I want only three
49. 2:06characters. So you get the last three
50. 2:08characters which is D E F. So we get
51. 2:12that. Okay, so those are left and right
52. 2:15functions in SQL Server. Now, another
53. 2:18very useful function in SQL Server is
54. 2:20the car index function. At first, it may
55. 2:22it may sound, you know, a little
56. 2:24complicated to use that, but it's pretty
57. 2:26simple. Okay. If you look at this
58. 2:28function, it has got three parameters.
59. 2:31Okay. Um the expression to find,
60. 2:33expression to search, and start
61. 2:35location. Let's look at an example which
62. 2:37makes it clear.
63. 2:39Now let's say I have an email address
64. 2:41something like this uh maybe sarah
65. 2:45aa.com.
66. 2:47So a aaa.com is the domain. Now let's
67. 2:50say within this email I want to find the
68. 2:55index of this at symbol. How do I do
69. 2:58that? I can make use of the care index
70. 3:01function. Okay. So
71. 3:05select I can use the car index function
72. 3:08and if you look at the car index
73. 3:09function it expects three parameters you
74. 3:12know the first parameter is the
75. 3:14expression that you want to search for
76. 3:16it could be a string or a character now
77. 3:18in our case it's a character we want to
78. 3:20search the at symbol so I want to search
79. 3:23the at symbol
80. 3:27and then the next parameter is another
81. 3:29expression of type this is nothing but
82. 3:32the expression which you want to search.
83. 3:34So I want to search for this at symbol.
84. 3:36I want to find the index of this at
85. 3:38symbol within this given expression.
86. 3:42Okay. And then um the third optional
87. 3:45parameter is the starting position. So
88. 3:47if you look at that the third parameter
89. 3:49is the start location. Now do you want
90. 3:52to start at the beginning of this
91. 3:54expression or somewhere in between?
92. 3:56Okay. Now let's assume that you have
93. 3:59maybe two or three at symbols in that
94. 4:02given expression and you want to find
95. 4:04the last one. Okay. So in those
96. 4:07circumstances you can use you know the
97. 4:09starting position. Okay. So let's say in
98. 4:12our case the starting position is one
99. 4:15and that's optional. If you don't
100. 4:17specify that parameter it's going to
101. 4:18anyway start at you know position one of
102. 4:21the string. So now when we execute this
103. 4:24we should get the index of the at symbol
104. 4:26which is five. So if you look at this 1
105. 4:292 3 4 5. So the index of at symbol you
106. 4:34know is at the fifth location. Okay. So
107. 4:36we get the index of that letter. So car
108. 4:38index is basically used to return the
109. 4:40starting position of the specified
110. 4:43expression in a character string. And
111. 4:44keep in mind start location is optional.
112. 4:48All right. Now another very useful
113. 4:50function is substring as the name itself
114. 4:52says substring. I want a part of the
115. 4:56string. So substring basically returns
116. 4:58part of the string from the given
117. 4:59expression. Now look at this. If you
118. 5:02want a part of a string from the given
119. 5:04string then obviously you will have to
120. 5:07tell okay the string itself from which
121. 5:09you want that part and then where you
122. 5:12want to start and how many characters
123. 5:14you want. Okay. So those are the three
124. 5:16parameters here. the the actual
125. 5:18expression where do you want to start
126. 5:21and and how many characters you want.
127. 5:23Okay, let's see that in action. Now
128. 5:25let's say if you look at this um you
129. 5:28know email address sarah.com what I want
130. 5:31is I want only the domain part. Okay,
131. 5:34here the domain is a aaa.com. I want
132. 5:37just the domain part. How do I do that?
133. 5:39I can use substring. Okay. So, select I
134. 5:44can say substring
135. 5:47of the expression. The expression is
136. 5:49nothing but this email.
137. 5:52And then you you want to specify the
138. 5:55starting position. The starting position
139. 5:57is 1 2 3 4 5. Okay. So, you don't want
140. 6:01anything until here.
141. 6:03You want, you know, starting at position
142. 6:06six. So, I want starting at position
143. 6:08six. And then how many characters? 3 4 5
144. 6:136 seven characters. So seven. So what is
145. 6:16this going to do? This is going to give
146. 6:18us that substring from this entire
147. 6:21email. So when we execute this, we
148. 6:23should get only the domain part. Okay.
149. 6:25So these two functions are extensively
150. 6:27used car index and substrings especially
151. 6:30when you want to manipulate strings.
152. 6:33So substring function returns substring
153. 6:35part um from the given expression. Okay.
154. 6:38Now if you look at the way we have used
155. 6:39this function, we have hardcoded these
156. 6:42values. You know at which position do
157. 6:44you want to start and how many
158. 6:46characters do you want. Now if you
159. 6:48remember um we want to start at I mean
160. 6:52how did you say that we want to start at
161. 6:54position six you know because the at
162. 6:57symbol starts there. We have counted
163. 6:59that and then we specified okay after
164. 7:01the at symbol position you know from
165. 7:04that position onwards we want the
166. 7:07substring okay but this is a little
167. 7:09dangerous let's assume you know an email
168. 7:12address is of like something like uh uh
169. 7:15maybe pam which is only three characters
170. 7:18pam u bbb.com
171. 7:21so if you look at this email address it
172. 7:23has got only three four records now if I
173. 7:26pass this name pass this email address
174. 7:32to this substring function. Look at
175. 7:34what's going to happen. It doesn't give
176. 7:35us exactly what we want. We only got
177. 7:37bb.com. It knocked off one letter.
178. 7:40That's because you want to start, you
179. 7:42know, you want the substring from 1 2 3
180. 7:454 from the fifth location. Okay. But
181. 7:48here it's the sixth location that we
182. 7:51have specified. Now when we do this, we
183. 7:53get that correctly. So that's the issue
184. 7:54with hard coding. So instead of hard
185. 7:57coding what you can do basically you can
186. 7:59use this car index function which will
187. 8:01calculate you know the index
188. 8:04dynamically. So you want the car index
189. 8:06of whatever.
190. 8:09So I want the car index here it is
191. 8:11spambb.com.
192. 8:13So instead of hard coding that we can
193. 8:16just pass the email address.
194. 8:18And now when we execute this we should
195. 8:20get the same result except that okay and
196. 8:24another thing look at this since we have
197. 8:26hardcoded that. Okay. And
198. 8:30if you look at this one we are actually
199. 8:32getting rid of um m and we got this
200. 8:36extra letter at symbol there. And to get
201. 8:39that because we don't want to start at
202. 8:41that position instead we want to add one
203. 8:44because the car index of h symbol will
204. 8:47give you 1 2 3 4 but you don't want to
205. 8:50start at fourth location you want to
206. 8:52start at fifth location. So add one to
207. 8:55that. So now when you execute this you
208. 8:57should get what you expect. Okay. And
209. 9:01similarly you can also um you know
210. 9:04dynamically calculate the number of
211. 9:06characters you want. How how can you
212. 9:08calculate the number of characters you
213. 9:10want within that string? Look at this.
214. 9:12Um you know in this string I I want to
215. 9:17start at this location. Okay. And that
216. 9:20you have specified using care index.
217. 9:22Okay. And the next thing that you have
218. 9:24to tell okay from this location how many
219. 9:27characters do you want? Okay. Now if you
220. 9:30want to get the total number of
221. 9:31characters that are available here,
222. 9:33don't you think if you get the total
223. 9:35length of this string and then subtract
224. 9:38the car index of H symbol because the
225. 9:41car index of H symbol what will it give
226. 9:43you? It will give you 1 2 3 4 and the
227. 9:46total length 4 + 71.
228. 9:50Okay, from 11 subtract four.
229. 9:54Okay, which will give us seven.
230. 9:58So
231. 10:00we can say okay I want the car index of
232. 10:03this one which will give us the car
233. 10:06index of this symbol and I want the
234. 10:08total length of the string. So if you
235. 10:10want the total length of the string what
236. 10:12you can do you can use length function
237. 10:14and we have spoken about this in the
238. 10:16previous session. So length function of
239. 10:19what is that of the email
240. 10:24and then from that length we want to
241. 10:26subtract the car index
242. 10:32of the symbol.
243. 10:42Okay. So now if we execute this now we
244. 10:46don't have those values hardcoded. Now
245. 10:48this select statement should work with
246. 10:51um any emails.
247. 10:54Look at that we get the exact same
248. 10:56output. Now at beginning it may sound a
249. 10:58little complex. Okay there are a lot of
250. 11:00function calls here and it it sounds a
251. 11:02lot of confusing but trust me it's very
252. 11:04simple if you understand how car index
253. 11:07substring and length function works.
254. 11:09It's just that instead of hard- coding
255. 11:11the values, we are calling one function
256. 11:13inside another function. Okay. Now, this
257. 11:15forms the basis for this simple
258. 11:18real-time example that we have. Let's
259. 11:19say I have our employees like this, the
260. 11:21first name, last name, and their email.
261. 11:23And I want uh you know the total
262. 11:28by domain. Okay, for example, in
263. 11:31gmail.com, how many how many users are
264. 11:33there with gmail.com domain? How many
265. 11:35users are there with a.com?
266. 11:38Okay, I want to find that and to do that
267. 11:41we use exactly the same logic that we
268. 11:44have done here but instead of you know
269. 11:47hard coding the email address like this
270. 11:50we will be passing in the email column
271. 11:52from our table. Okay, so let's see how
272. 11:55to do that.
273. 11:58So when we execute this query we have
274. 12:00all the employees here. All we want is
275. 12:02an output like this. Okay, total email I
276. 12:05mean email domain and the total count.
277. 12:09All right, so what do we want in our
278. 12:11output? We want the email domain first.
279. 12:14So select.
280. 12:16So we want the email column in the
281. 12:18output. But we don't want the complete
282. 12:21email. We just want the domain part.
283. 12:24Okay. And remember to get the domain
284. 12:26part, we have already written this
285. 12:28substring function. So this will give
286. 12:31you the domain part. So copy that.
287. 12:36But then wherever you have hardcoded
288. 12:39this email,
289. 12:41we want to pass in the name of the
290. 12:44column. It's as simple as that.
291. 12:47So email column.
292. 12:52Pass it there.
293. 13:02Okay. So from where we want from tbl
294. 13:05employee table.
295. 13:08Okay. So when we execute that we should
296. 13:10get all the domains and you want to give
297. 13:13a meaningful name for that as email
298. 13:16domain maybe.
299. 13:18So when we execute that we get the email
300. 13:21domain but we are getting all of the
301. 13:23domains. Now if you have watched part
302. 13:26you know 11 of this video series where
303. 13:29we have spoken about group by now it
304. 13:31should be pretty simple to group those
305. 13:33emails by the domain. Right now you can
306. 13:35just group them by this particular
307. 13:38column and you're done. So the next
308. 13:41thing that we want is the count of
309. 13:43employees. count of maybe email
310. 13:50as total
311. 13:53and then since you are using count
312. 13:55aggregate function you want to use
313. 14:00group by so I want to group by by this
314. 14:05expression.
315. 14:14Okay.
316. 14:24Uh invalid column name count of email.
317. 14:30Okay.
318. 14:32So you should get the output that we
319. 14:34expect email domain and the total. Okay.
320. 14:37And there are other ways of achieving
321. 14:39this one. But since we are talking about
322. 14:42string functions, I thought it's better
323. 14:44to use those functions and achieve this
324. 14:46output.
325. 14:49On this slide, you can find resources
326. 14:50for ASP.NET and C# interview questions.
327. 14:53That's it for today. Thank you for
328. 14:54listening. Have a great day.