YT YouTube2Text

YouTube2Text → Transcripts → Built in string functions in sql server 2008 Part 22

Built in string functions in sql server 2008 Part 22 — Transcript

by kudvenkat · 2,665 words · 372 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00Hello, welcome to Proim Technologies.
0:01I'm Wenut. This is part 22 of SQL
0:04Server. In this session, we'll learn
0:06about the commonly used built-in string
0:08functions that are available in SQL
0:10Server. Functions in SQL Server are
0:12broadly divided into two categories.
0:15Userdefined functions and system
0:17functions. We haven't spoken about
0:19userdefined functions. We will be
0:21talking about them in a later session.
0:23In this session, we'll talk about all
0:25the available system functions.
0:29Now if you want to see all the system
0:31functions that are available in SQL
0:33server expand programmabilities folder
0:36within that functions system functions
0:38and you should see you know all the
0:40system functions available here
0:42categorized into their own categories
0:44depending on the functions they perform.
0:46Now if if you want to you know aggregate
0:49values then you use some of the
0:51aggregate functions like sum, count etc.
0:54And if you want to work with date and
0:56times, we have date and time functions
0:58which we'll be talking about in a later
1:00session. And similarly, if I want to
1:02work with strings, I have sing string
1:04functions. Today, we will be looking at
1:06some of the commonly used string
1:09functions. Now, we have this ASKI string
1:12function. Now, we use ASKI string
1:14function to return the ASKI code value
1:17of the given character expression. Now,
1:20every character has got an associated
1:22ASKI code value. So if you want to
1:24retrieve the ASKI code value of a given
1:26character expression then we use the ASI
1:29function. Okay let's see how to use
1:32that. So look at this. This is the key
1:36function.
1:37Now select as key and look at this. The
1:42moment I open this parenthesis, you
1:44know, Visual Studio SQL Server, I mean
1:47SQL Server, IntelliSense shows me, okay,
1:51this is expecting a var data type, an
1:54expression of type var. And this is
1:56going to return an integer. Okay, so
1:59this is this is one way of identifying
2:02what you need to pass to the function
2:04and what it returns to you back. Okay,
2:06another way is if you just expand the
2:09function itself, it should tell you okay
2:11the parameters that it is expecting is
2:14an expression of type care or where care
2:18and it returns an integer because ASKI
2:20code value is an integer between 0 and
2:23255. It's going to return an integer for
2:26a given character expression. Okay. So I
2:29want the ASI code value for capital
2:31letter A. So when we select that press
2:35F5 65 is the ask key code value for
2:38capital letter A. Now what happens if I
2:40say ABC if I pass three of the
2:43characters like this look at this
2:44because this is expecting a var. Okay
2:48ABC is a var. So when I pass that what
2:51happens it's going to return the ASI
2:53code value of the first character which
2:55is a in this case. Now if I just say get
2:59me the ASKI character value of BC then
3:02it's going to give me the ASI uh code
3:04value of letter B which is 66. Okay. So
3:09we use ASKI code I mean ASKI function to
3:11return the ASKI code value of the given
3:14character expression. The next function
3:16that we commonly use is the character
3:18function. Okay. Now what does this
3:20character function do? It converts an
3:23ASKI code to a character. Okay. Now see
3:27the AS key character reverses what ASI
3:31ASKI function does. Now ASKI function
3:33returns the ASI code value whereas the
3:35character function returns the uh
3:38character associated with the ASI code
3:40value. And we know that the ASI code
3:43values are between 0 and 255. Okay. And
3:46we know the ASI code value for capital
3:49letter A is 65. Now let's say I want to
3:53print alphabets from A through Z. How do
3:57I do that? You know I can make use of
3:59this as key codes and then create a loop
4:01and then print them. Let's see how to do
4:03that. Now let's say if I use the car
4:06function look at this when I expand you
4:08know the IntelliSense it shows it is
4:11expecting an integer expression an
4:14integer and then what is it going to
4:16return to you? It's going to return a
4:17character. Okay. So for example if I
4:20pass 65 you know that 65 is the ASKI
4:24code value of capital letter A. So when
4:26I press you know execute this I should
4:29get letter A. Now let's say I want to
4:31print alphabets capital letter alphabets
4:34from A through Z. How do I do that? I
4:36can use a while loop to do that and the
4:39car function. Let's do that. So declare
4:42a variable of type integer. Let's call
4:44that start integer. initialize that to
4:48maybe 65 because capital letter A starts
4:51at 65. So 65 and then what I want to do
4:55from 65 there are 26 alphabets in
4:58English language. So 65 + 26 is um 91.
5:03So 91 until 91. Okay. So
5:08while at start is less than or equal to
5:1590 begin end what do we want to do? We
5:19want to print the character associated
5:22with that asi code value. So what we can
5:27do is select car of instead of hard
5:31coding the value what we want to do we
5:33want to pass this integer variable. So
5:36when we pass this integer variable you
5:39know it loops through first time it will
5:40be 65 so it will print capital letter a
5:42next we need to increment
5:45that. So set at start is equal
5:49to at start + 1. So we increment it by 1
5:5366. 66 is the ask code value of capital
5:56letter B. So that gets printed out. So
5:59instead of selecting it, we'll just
6:00print
6:01it. Okay. So let's execute this and see
6:04what's going to happen. It should print
6:06capital letter A through zed. Okay. So
6:10you can see A B C D printed until zed.
6:13Now let's say I want to print small
6:15letters from A through zed. How do I do
6:18that? Very simple. define the ASI code
6:20value for small A. So press F5. 97 is
6:24the ASI code value of small A. So 97 +
6:2926 is equal to 123. So what we want to
6:33do? We want to start at
6:3797 and we want to loop through 122 and
6:42print that. So this time when we execute
6:44this query it's going to print all the
6:46small cap I mean small alphabets. Okay.
6:50Similarly if I want to print numbers 0
6:52till 9. How do I do that? Get the ASKI
6:55code value of
6:57zero which is 48. So start at
7:0148 and then 48 + 10 is
7:04[Music]
7:0558. So 58 and then print that. That's
7:09about it.
7:12So 0 till 9 you know maybe we want to
7:15say 57 because there is one extra colon
7:21printed. Okay. So this is how we can
7:24make use of the character function to
7:26print you know alphabets whatever
7:29numbers
7:30etc. Okay. And then the other important
7:33functions that we commonly use is Ltrim.
7:36You know the name itself is saying Ltrim
7:40left trim trim on the left side for a
7:44given character expression. It's going
7:45to remove the white spaces on the left
7:47hand side of that character expression.
7:50Okay, let's see that in
7:52action. So for example, let's say I have
7:56something like this
7:57select. There is a name. Okay, I have
8:00some spaces there. Hello. Now when we
8:03execute this, it just shows that hello
8:06with the you know white spaces on the
8:08left hand side. Now let's say I want to
8:09remove those white spaces. So if you
8:12want to remove the white spaces on the
8:14left hand side of the given string, you
8:15can use the lrim function and pass the
8:18string to that. It's going to return you
8:20know a string that has the white spaces
8:22removed. So we have the white spaces
8:24removed there. Okay. Now let's look at a
8:27practical example of app applying this.
8:30So select star from tbl employee. If you
8:33look at the rows in this table, look at
8:35the first name, you know, some of the
8:37names in this table have got, you know,
8:39spaces in front of them. Now let's say I
8:41want to remove all of the spaces in
8:43front of them. How can I do that? I can
8:45make use of the cell trim function. So I
8:48want to say I want first name and maybe
8:51middle name and last name columns. Okay.
8:55Now when we execute this, we get all
8:56those columns. But the problem is look
8:58at this. they are having these white
9:00spaces in the front and they don't look
9:02good. So if you want to remove the white
9:04spaces, you can simply use the lrim
9:06function and it should knock off those
9:08white spaces at the beginning of the
9:11name. And obviously this column name we
9:15have lost it. So we can give it an
9:16aliyah's name if you want as first
9:21name. and you execute
9:26that you should get the first name as
9:28expected. And similarly along the same
9:30lines we have R trim. If you want to
9:33remove the white spaces on the right
9:35hand side of the given character
9:36expression then you can make use of R
9:38trim function. So let's see how to do
9:40that. So let's say there is this hello
9:43word and on the right hand side I have
9:45white spaces. Now if I want to remove
9:48those spaces there I can make use of our
9:51trim. Press F5. So it removes the white
9:54spaces on the right hand side. We can't
9:55see that but it it trust me it removes
9:58them. Now let's say let's execute this.
10:03Now these names have got uh you know
10:07white spaces on the right hand side as
10:09well. Okay. Uh so I if you want to
10:11remove the white spaces on the right
10:12hand side you can use our trim function.
10:15Let's see them first. Now let's say I
10:17not only want the first name, middle
10:19name and last name. I al I also want
10:22their full name. Okay, their first name
10:25and then space middle name space last
10:27name. How do I get that? First name plus
10:30give a small space and then maybe middle
10:34name and then give a space and then last
10:39name. So essentially what we are doing
10:41here is we are concatenating the first
10:44name, middle name and last name together
10:47using the plus sign. So now when we
10:49execute
10:51this you should see the difference. Look
10:54at that. This is going to be our full
10:56name the last column. Let's give it an
10:58alias called
11:00as full
11:02name. So if you look at this now we have
11:05the full name but look at this. Because
11:07of the spaces on the left and right side
11:09of the first name they don't look right.
11:12Okay. So what we want to do is knock off
11:14the white spaces on the left hand side
11:16of the first name and the white spaces
11:19on the right hand side of the first
11:20name. So that look at this Todd Cartner
11:23has got lot of white space you know on
11:25the right hand side. So how do we remove
11:27both the left and right side whites
11:30spaces use ltrim and rtrim together.
11:34Okay. So if you pass the first name here
11:37to
11:38ltrim, what it's going to do, it's going
11:40to
11:41remove the white spaces on the left hand
11:44side and then return the string for the
11:46returned string. Remove the white spaces
11:48on the right hand side as well.
11:51So you're passing one function inside
11:54another function. So now when we execute
11:56this, look at what's going to happen for
11:58the output. It appears as we expect.
12:00First name, middle name, and last name.
12:03Okay. So we can use L trim and R trim
12:06basically to remove white spaces on the
12:08left hand side and on the right hand
12:09side of the given character expression.
12:12And similarly if you want to convert you
12:14know uh letters into lower case or
12:16uppercase you can use lower and upper
12:19functions. Okay let's see them in
12:22action. Um let's say I want all the
12:24first names in the upper case. So what
12:26you can do you can use the upper
12:29function. So upper of this first
12:33name. Okay. And similarly let's do that
12:37you know lower for last name. So last
12:41name is going to be converted into lower
12:43case. Okay. So let's execute this. So
12:47look at that. All first names are
12:49converted into uppercase. All last names
12:51are converted into lower case. Now since
12:54you're using a function you will lose
12:56the column name. If you want the column
12:58name, use the alias
13:00there. All right. So that's about upper
13:04and lower functions. And we have reverse
13:06function as well. If you want to reverse
13:08all the characters in the given string
13:10expression, you use reverse function.
13:12Let's say I want the names the first
13:15names of the function, you know, the
13:16first name of the person to be reversed.
13:18Sam should be sam should become mas. How
13:22can I do that? Use the reverse function.
13:27So when you use the reverse function,
13:28look at this. I'm chaining all these
13:30functions together. Um,
13:34okay. So reverse. So what's going to
13:37happen? It's going to reverse that SAM
13:39name. MAS, M RAM. Okay. It's going to
13:43reverse
13:45that.
13:47Okay. Interestingly, it has again
13:50reintroduced that white spaces, but you
13:51know how to get rid of them.
13:55And then finally length function. Length
13:57function can be used to calculate the
13:59length of the given string. Okay. So if
14:02you look at the name of the person, you
14:04know the first name of the person. Let's
14:07get the first
14:11name from TBL employee. So if you look
14:15at the first name now, I want the their
14:17first name and the number of characters
14:19in their first name. How do I do that?
14:21Okay, this is the first name itself. So
14:23if I want to find the length you know
14:25the number of characters in their first
14:27name I can use length function pass it
14:30the name of the column that's all and
14:33maybe you can give it an alias name
14:36maybe
14:40total
14:42characters. So when we execute this you
14:45should see the first name and the total
14:46characters and you and you and now you
14:49should understand look at this there are
14:50only three characters but I'm getting
14:51five characters. Why is that? because
14:53there are white spaces at the beginning
14:55of the string. So two white spaces
14:58that's why it's giving including those
14:59white spaces. Okay. So if you don't want
15:02to count the white spaces then what you
15:05need to do is you need to first uh ltrim
15:09that remove the white spaces at the
15:11beginning of the string. Now when we
15:14execute this it should give us the
15:19correct count of the letters. Okay.
15:23Three.
15:26Three.
15:28Okay. So, this is correct. Now, on the
15:31right hand side, if you want to remove
15:33Okay, look at this. The count function,
15:36I mean the length function returns the
15:38count of total characters in the given
15:40string expression excluding the blanks
15:42or white spaces at the end of the
15:44expression. Even if there are white
15:45spaces at the end of the expression, it
15:47doesn't count them. But if they are at
15:49the beginning or in the middle of a
15:51given string then it counts them. Okay.
15:54So these are some of the you know
15:55commonly used string functions. We'll be
15:57talking about the rest of the commonly
15:59used string functions like car index
16:01substring etc in the next video
16:04session. On this slide you can find
16:06resources for ASP.NET and C# interview
16:09questions. That's it for today. Thank
16:10you for listening. Have a great day.

About this transcript

This page contains the full transcript of Built in string functions in sql server 2008 Part 22 by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 2,665 words across 372 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.