Replicate, Space, Patindex, Replace and Stuff string functions in sql server 2008 Part 24 — Transcript

Full transcript

1. 0:00hello welcome to pre Technologies I am
2. 0:02wkit this is part 24 of SQL server in
3. 0:06this session we'll learn about the rest
4. 0:08of the built-in string functions that
5. 0:10are available in SQL Server specifically
6. 0:13we'll be looking at replicate space Pat
7. 0:16index replay and stuff functions before
8. 0:20continuing with this session I strongly
9. 0:22recommend to watch Parts 22 and 23 of
10. 0:25this video
11. 0:26series all right the first function that
12. 0:28we'll look at is the replicate function
13. 0:31now if you look at the syntax of the
14. 0:33replicate function it has got two
15. 0:35parameters string to be replicated and
16. 0:38how many times you want to replicate
17. 0:41that given string so this function
18. 0:43repeats the given string for the
19. 0:45specified number of times let's
20. 0:48understand this with an example if you
21. 0:50look at here I have the select Clause
22. 0:53select and then we have this replicate
23. 0:55function and to this we are passing this
24. 0:57Pim keyword that's the first parameter
25. 1:00and the second parameter how many times
26. 1:03you want to replicate this I want to
27. 1:04replicate this three times so when we
28. 1:06execute this praim word along with the
29. 1:10space next to it will be repeated three
30. 1:13times okay now here is a simple
31. 1:15practical example of the replicate
32. 1:17function if you look at this we have got
33. 1:20first name last name and email of the
34. 1:22person now what we basically want to do
35. 1:24we have seen in many of the realtime
36. 1:26applications they don't reveal full
37. 1:28email address uh but maybe they will
38. 1:31just reveal that you know using some
39. 1:33masks um you know uh so basically we are
40. 1:37masking here the email address of a
41. 1:39person using five star symbols and to do
42. 1:42that we are using the replicate function
43. 1:45and using the replicate function itself
44. 1:47is pretty simple here look at this in
45. 1:49the output we want the first name last
46. 1:50name and you know the mask email so
47. 1:54first name last name we will select it
48. 1:56as usual but we want to mass the email
49. 1:58column so first what we are doing we are
50. 2:01getting the two the first two letters of
51. 2:04the email okay and obviously if you want
52. 2:07a sub string of a given string we use
53. 2:10the substring function and we have
54. 2:12spoken about substring function car
55. 2:14index length in the previous session in
56. 2:17a great detail so if you want to know
57. 2:20how to work with that please watch the
58. 2:21previous session so this first piece
59. 2:24here it gives us the first two letters
60. 2:26of the email and to that two letters we
61. 2:29are actually con catenating using the
62. 2:31plus sign five star symbols look at this
63. 2:34this is where we are using the replicate
64. 2:36function I want to replicate this star
65. 2:38five times okay and then to the five
66. 2:42stars we are then appending the add
67. 2:44symbol and the domain okay aa.com or
68. 2:48bbb.com whatever okay and again to get
69. 2:52the add symbol and the domain we are
70. 2:54using the subring because we want the
71. 2:56rest of that okay so we are basically
72. 2:59using replicate function to mask the
73. 3:02email one of the practical uses of
74. 3:04replicate
75. 3:05function all right the next function
76. 3:07that we'll talk about is the space
77. 3:09function and this is a pretty simple
78. 3:11function to understand okay now if you
79. 3:13want a space in a SQL query you know you
80. 3:16basically can use um you know something
81. 3:20like this so if I just want a
82. 3:23space I can say
83. 3:26select and you know within the single
84. 3:28code we can specify how many other SPAC
85. 3:30spaces I want for example if I want five
86. 3:33spaces 1 2 3 4 5 I have all the five
87. 3:36spaces there and it shows I mean
88. 3:38obviously you can't see a space but
89. 3:41that's how it is now this is a little
90. 3:43odd let's say if I want like 10 spaces
91. 3:46this becomes you know little odd to look
92. 3:48at that's why using the space function
93. 3:51is much better so when we say select
94. 3:54space of maybe how many ever you want I
95. 3:57want five space and this is more
96. 3:58readable as well now in the first case
97. 4:01you have to literally count 1 2 3 4 5
98. 4:04you have to do that I mean here by
99. 4:06looking at that you can say okay there
100. 4:09is going to be five spaces now if I
101. 4:11execute this line okay obviously we
102. 4:13can't see them
103. 4:14but okay so what we basically want to do
104. 4:18with the space function is um you know I
105. 4:20want to insert you know five spaces
106. 4:23between the first and last name you know
107. 4:25I want the full name of the employee but
108. 4:28between their first and last name name I
109. 4:30want five spaces how do I do that we
110. 4:32just select the first name concatenate
111. 4:34that with five spaces using the space
112. 4:36function and then their last name and
113. 4:38give it an alas name full name from
114. 4:40table employee pretty
115. 4:43simple and obviously if you want to do
116. 4:45the same thing instead of using the
117. 4:46space function you can literally put
118. 4:48five spaces within single quotes here
119. 4:51but then that's not that
120. 4:55readable okay so the next very useful
121. 4:58function that we'll talk about is the
122. 5:00pat index standing for pattern index now
123. 5:04this pattern index function Returns the
124. 5:07starting position of the first
125. 5:10occurrence of a pattern in a specified
126. 5:12expression so in the in the given
127. 5:14expression okay it will find the first
128. 5:18occurrence of a pattern that you want
129. 5:20okay in a way Pat index is similar to
130. 5:24car index look at that pattern index
131. 5:27character index now both of them return
132. 5:30you know the index of a string but with
133. 5:33Pat index you have the flexibility of
134. 5:35using wild cards but with c Index you
135. 5:38cannot use wild cards that's the only
136. 5:40difference and both of them are
137. 5:42extremely
138. 5:43useful okay so here is a practical
139. 5:46application of this pattern index now
140. 5:48what we are basically doing here is we
141. 5:50are finding the look at the query here
142. 5:54so we're getting the email and within
143. 5:57the within the email I am finding this
144. 6:00pattern you know at aaa.com I want you
145. 6:04know the index the start index of at
146. 6:09aaa.com okay before that I don't care
147. 6:12what what is there I mean do we have
148. 6:14three characters four characters is it
149. 6:16Sam Ram John whatever we don't care
150. 6:19that's why we are using the wild cards
151. 6:21again we have spoken about wild cards in
152. 6:23a great detail in the previous sessions
153. 6:24of this video series so please check
154. 6:26back if you don't know what are wild
155. 6:28cards and how to use them okay so we are
156. 6:30specifying okay you know I want the
157. 6:34pattern index of this at aa.com but I
158. 6:39don't care what is really before that
159. 6:42okay before that add symbol and then we
160. 6:45are using so I want to find this pattern
161. 6:48in this email column of the employee
162. 6:50table okay so obviously when we execute
163. 6:54that query you should
164. 6:56see so if you look at this without I'm
165. 6:59executing this query without the wear
166. 7:00Clause so when I execute without the
167. 7:03wear Clause look at this wherever we
168. 7:05have
169. 7:06aaa.com at
170. 7:08aaa.com you know we are getting the
171. 7:10index there the first occurrence the
172. 7:12first occurrence here is 1 2 3 at the
173. 7:15fourth position we get that but look at
174. 7:17this here we got for at
175. 7:21ccc.com if you look at this string we
176. 7:24don't have at
177. 7:26aaa.com string within that email mail so
178. 7:30that why that's why it returns zero so
179. 7:32if the given pattern is not found in the
180. 7:34given string then it returns zero okay
181. 7:38and so obviously if you just want the
182. 7:41emails for which you have this index
183. 7:44number then obviously you can use a wear
184. 7:46clause and filter that with greater than
185. 7:49zero and it will only give us those
186. 7:51emails which has got that pattern we
187. 7:54want which is at
188. 7:57aaa.com so that's about Pat index and
189. 8:01and it's very much similar to car index
190. 8:03as I told
191. 8:05you all right the next function that
192. 8:07we'll talk about is the replace function
193. 8:10as the name says it's going to replace
194. 8:11something for us so it basically again
195. 8:14this function is really useful we
196. 8:16replace all occurrences of a specified
197. 8:19string with another string okay so I
198. 8:22have the string you know expression and
199. 8:25a pattern and a replacement value okay
200. 8:28with in this original string
201. 8:29whatever pattern you specify okay we
202. 8:32want to replace that pattern with this
203. 8:35value okay so if you want a very simple
204. 8:37example look at this we have got first
205. 8:39name last name email now what I want to
206. 8:41do is I want to replace the with net
207. 8:45it's pretty simple to do all you do is
208. 8:48okay use the replace function obviously
209. 8:51so for the replace function you specify
210. 8:53in the email column I want to
211. 8:58replace with with.net it's as simple as
212. 9:01that and give it to Alas as converted
213. 9:03email from table employee let's see you
214. 9:06know this query in action again these
215. 9:09functions are very simple to use all you
216. 9:11have to understand about is the
217. 9:13parameters that it
218. 9:17expects so let's execute this
219. 9:21query you know the email and the conver
220. 9:24Emil Dooms got converted into
221. 9:27net okay so the final function that
222. 9:30we'll talk about uh is the stuff
223. 9:32function um you know as the name States
224. 9:35it's going to stuff something okay uh so
225. 9:38basically what this function does is
226. 9:41it's slightly different to replay I mean
227. 9:43it's going to replace some characters
228. 9:45but then look at this the stuff function
229. 9:48it expects a
230. 9:50string and then within that string at
231. 9:53the start position you specify and for
232. 9:57how many characters the length okay okay
233. 10:00and the replacement expression so what
234. 10:02what what the stuff function going is
235. 10:04going to do is you can say okay start at
236. 10:07position two if you look at the way we
237. 10:09have used the star function here I mean
238. 10:11it's very clear here uh first let's
239. 10:13understand the output that we want you
240. 10:16know we want the first character from
241. 10:18the email okay we want to leave it there
242. 10:21but then for the
243. 10:24next you know three characters after the
244. 10:28first character for the next three
245. 10:30characters I want to replace the next
246. 10:32three characters you know in this case
247. 10:34am and add symbol I want to replace that
248. 10:37with five stars and then whatever
249. 10:40follows that we just want that okay um
250. 10:44so basically this is another kind of
251. 10:47masking okay uh using the stuff function
252. 10:50and look at this if you look at the way
253. 10:51we have used this stuff function uh we
254. 10:54are saying okay work on the email column
255. 10:57on the email column start at letter two
256. 11:01okay which means leave the first
257. 11:02character but start at number two and
258. 11:05then for the rest three characters you
259. 11:07know if you take Sam at a.com okay start
260. 11:10at two and the next three characters a m
261. 11:13and at for these three symbols replace
262. 11:17those three characters with five star
263. 11:21symbols okay and then obviously we are
264. 11:23giving it an alas as stuffed email and
265. 11:26we have that there okay so basically
266. 11:29stuff function inserts this replacement
267. 11:32expression at the start position
268. 11:34specified along with removing the number
269. 11:38of characters that you have specified
270. 11:40using the length
271. 11:42parameter on this slide you can find
272. 11:44resources for asp.net and cop interview
273. 11:47questions that's it for today thank you
274. 11:48for listening have a great day