Commonly used string operation in SQL
There are many string operations which you used quite often in SQL. For me, it happened many times that I did not work on sql for a long time and forgot many of this commonly used string formats. Here, I have collected all such functions so that there is no need of searching such operation on internet again and again.
Use + operator to concatenate various string/Columns
SELECT First_Name + '' + Last_Name + ' earns' + Salary AS MoneySalary FROM Employee;
Combining numeric and date data types
SELECT 'Employee '+EmpId+' was born on ' + CONVERT(varchar(12), 'BirthDate', 101) FROM Employees
WHERE EmpId = 51;
Adding spaces in select query
SELECT 'hare' + ' ' + 'krishna' AS 'Full Name'
Searching for escape or slash character in string
SELECT EmployeeKey,FirstName
FROM DimEmployee
where FirstName like '%''%' or FirstName like '%\%%' escape '\';
-Any operation performed on null, results in null value
SELECT FirstName + NULL FROM DimEmployee will return NULL;
INSERTING DATA CONTAINING APOSTROPHES
Add two apostrophes to show single apostrophe in input string
select 'Fifth Product''s Name'
Wildcard Search Strings
% Any string of zero or more characters.
_ Any single character.
[ ] Any single character within the specified range (for example, [a-f]) or the specified set (for example, [abcdef]).
[^] Any single character not within the specified range (for example, [^a-f]) or the specified set (for example, [^abcdef]).
LIKE '[k-l]%'
Get number of delimiters
declare @mystring varchar(200)
set @mystring='Hare,Krishna,Hare,Rama'
select len(@mystring) as 'original string length',
(len(@mystring)-len(replace(@mystring,',',''))+1) as 'No Of Split Character'
Note: Images used on this website
are either a production of Bhaktivedanta Book Trust(https://www.krishna.com), Iskcon
Foundation or were found in google search under "Free to use and share". If any
of the images presented here violates copyright issues or infringes anyone copyright
or are not under "Fair use", then please bring it to our notice. Read
Disclaimer for more.
Share this to your friends. One of your friend is
waiting for your share.
Related Articles
Need and advantages of using computed columns
Commonly used string Functions
Capitalise first character of every word
Different string functions in SQL-Part 2
Different string functions in SQL-Part 1
Commonly used string operation in SQL
Parse comma separated input string in SQL
Post Comment