Welcome to my blog post about Understanding String Data Types in SQL! Here, we’ll look into how SQL handles different types of text data. From knowing how long your text can be to how it’s stored, we’ll cover it all in plain language.
1.
SELECT ASCII('A');This query returns the ASCII value of the character ‘A’, which is 65.
2.
Declare @mychar as char(15)
set @mychar = 'Harshida'
select @mychar as NameResult,
len(@myChar) as StringLength,
DATALENGTH(@mychar) as TotalLengthHere, a variable @mychar is declared as a fixed-length string (char) with a length of 15 characters. The variable is assigned the value ‘Harshida’. The query then retrieves the value of @mychar, its length using LEN(), and its total length in bytes using DATALENGTH().
3.
Declare @mychar1 as char(15)
set @mychar1 = '' --
select @mychar1 as NameResult,
len(@myChar1) as StringLength,
DATALENGTH(@mychar1) as TotalLengthSimilarly, this block declares a char variable @mychar1 with a length of 15 characters and assigns an empty string to it. Then, it retrieves the value of @mychar1, its length, and its total length in bytes.
4.
Declare @mychar2 as varchar(15)
set @mychar2 = 'HarshidaABCDFEGHIJKLM'
select @mychar2 as NameResult,
len(@myChar2) as StringLength,
DATALENGTH(@mychar2) as TotalLength
This block declares a variable @mychar2 as a variable-length string (varchar) with a maximum length of 15 characters. It assigns the value ‘HarshidaABCDFEGHIJKLM’ to it and then retrieves its value, length, and total length in bytes.
5.
Declare @mychar3 as varchar(15)
set @mychar3 = '' --
select @mychar3 as NameResult,
len(@myChar3) as StringLength,
DATALENGTH(@mychar3) as TotalLengthSimilar to the previous block, this one deals with an empty varchar variable, showing its value, length, and total length in bytes.
6.
Declare @mychar4 as nchar(30)
set @mychar4 = 'Harshida'
select @mychar4 as NameResult,
len(@myChar4) as StringLength,
DATALENGTH(@mychar4) as TotalLength
This block declares an nchar variable @mychar4 with a length of 30 characters (each character occupies two bytes in memory in the case of nchar). It assigns the value ‘Harshida’ to it and then retrieves its value, length, and total length in bytes.
7.
Declare @mychar5 as nchar(30)
set @mychar5 = '' --
select @mychar5 as NameResult,
len(@myChar5) as StringLength,
DATALENGTH(@mychar5) as TotalLength
Similarly, this block deals with an empty nchar variable, showing its value, length, and total length in bytes.
8.
Declare @mychar6 as nvarchar(30)
set @mychar6 = 'Harshida'
select @mychar6 as NameResult,
len(@myChar6) as StringLength,
DATALENGTH(@mychar6) as TotalLength
This block declares an nvarchar variable @mychar6 with a length of 30 characters. It assigns the value ‘Harshida’ to it and then retrieves its value, length, and total length in bytes.
9.
Declare @mychar7 as nvarchar(30)
set @mychar7 = ''
select @mychar7 as NameResult,
len(@myChar7) as StringLength,
DATALENGTH(@mychar7) as TotalLength
Similar to the previous block, this one deals with an empty nvarchar variable, showing its value, length, and total length in bytes.
10.
Declare @mychar8 as nvarchar(30)
set @mychar8 = N'हर्षिदा' --Implicit Conversion
select @mychar8 as NameResult,
len(@myChar8) as StringLength,
DATALENGTH(@mychar8) as TotalLength
This block declares an nvarchar variable @mychar8 with a length of 30 characters. It assigns the Unicode string ‘हर्षिदा’ (Harshida in Hindi) to it. Then, it retrieves its value, length, and total length in bytes. Since it’s a Unicode string, each character occupies two bytes.
11. LEFT() | String Data Types in SQL
-- Example table
CREATE TABLE tbl_stringType (
id INT,
name VARCHAR(50)
);
-- Insert sample data
INSERT INTO tbl_stringType (id, name) VALUES
(1, 'Krishana Friend'),
(2, 'Harry Developer');
-- Using LEFT() to extract the first 4 characters from the name column
SELECT LEFT(name, 4) AS extracted_name FROM tbl_stringType;
| extracted_name |
|---|
| Kris |
| Harr |
12. RIGHT()
-- Using RIGHT() to extract the last 5 characters from the name column
SELECT RIGHT(name, 5) AS extracted_name FROM tbl_stringType;
| extracted_name |
|---|
| riend |
| eloper |
13. SUBSTRING() or SUBSTR()
-- Using SUBSTRING() to extract a substring starting from the 6th character with length 3
SELECT SUBSTRING(name, 6, 3) AS extracted_name FROM tbl_stringType;
| extracted_name |
|---|
| na |
| Dev |
14. LENGTH() or LEN()
-- Using LENGTH() to get the length of the name column
SELECT LENGTH(name) AS name_length FROM tbl_stringType;
| name_length |
|---|
| 15 |
| 15 |
15. CONCAT()
-- Using CONCAT() to concatenate id and name columns
SELECT CONCAT(id, ': ', name) AS concatenated_data FROM tbl_stringType;
| concatenated_data |
|---|
| 1: Krishana Friend |
| 2: Harry Developer |
16. TRIM()
-- Using TRIM() to remove leading and trailing spaces from the name column
SELECT TRIM(name) AS trimmed_name FROM tbl_stringType;
| trimmed_name |
|---|
| Krishana Friend |
| Harry Developer |
17. UPPER()
-- Using UPPER() to convert the name column to uppercase
SELECT UPPER(name) AS uppercase_name FROM tbl_stringType;
| uppercase_name |
|---|
| KRISHANA FRIEND |
| HARRY DEVELOPER |
18. LOWER()
-- Using LOWER() to convert the name column to lowercase
SELECT LOWER(name) AS lowercase_name FROM tbl_stringType;
| lowercase_name |
|---|
| krishana friend |
| harry developer |
C# Sharp programming exercises: String
1. Write a program that searches for the position of a substring within a string.
2. Check whether a given substring is present in the given string.
3. Write a program to Break String In Char
4. Write a program to Extract a substring from a given string
5. Write a program to Find All Substrings
6. Write a program to Count String Word
7. Write a program to Remove Vowel From String
8. Write a program to swap Two String without third variable
9. Write a program to check String IsPalindrome?
10. Write a program to Find the number of times a specific string appears in a string.
