Execute Dynamic SQL commands in SQL Server

In some applications having hard-coded SQL statements is not appealing, because of the dynamic nature of the queries being issued against the database server. Because of this sometimes there is a need to dynamically create a SQL statement on the fly and then run that command. This can be done quite simply from the application perspective where the statement is built on the fly whether you are using ASP.NET , ColdFusion or any other programming language. But how do you do this from within a SQL Server stored procedure? SQL Server offers a few ways of running a dynamically built SQL statement. These ways are: Writing a query with parameters Using EXEC Using sp_executesql Writing a query with parameters This first approach is pretty straightforward if you only need to pass parameters into the WHERE clause of your SQL statement. Let’s say we need to find all records from the Customers table where City = ‘London’. This can be done easily as the following example shows.

SQL String Functions Tutorial

SQL Server offers a plethora of string functions that empower developers to manipulate and transform text data efficiently. In this comprehensive guide, we will explore various string functions, unraveling their functionalities with examples.

1. CHARINDEX

The CHARINDEX function retrieves the index of a specified character within a given string. The syntax is as follows:


`SELECT CHARINDEX('S', 'MICROSOFT SQL SERVER 2000') as Result;

Result: 6

2. LEFT

The LEFT function extracts a specified number of characters from the left side of a string. Here’s how it works:


`SELECT LEFT('MICROSOFT SQL SERVER 2000', 4) as Result;

Result: MICR

Conversely, the RIGHT function extracts characters from the right side of a string:


`SELECT RIGHT('MICROSOFT SQL SERVER 2000', 4) as Result;

Result: 2000

4. LEN

To determine the length of a string, the LEN function comes in handy:


`SELECT LEN('MICROSOFT SQL SERVER 2000') as Result;

Result: 25

5. REPLACE

The REPLACE function replaces a specified portion of a string with a new value:


`SELECT REPLACE('MICROSOFT SQL SERVER 2000', 'MICROSOFT', 'MS') as Result;

Result: MS SQL SERVER 2000

6. STUFF

Using the STUFF function, you can replace a specified length of characters with a new pattern:


`SELECT STUFF('MICROSOFT SQL SERVER 2000', 11, 3, 'S.Q.L.') as Result;` 

Result: MICROSFT S.Q.L. SERVER 2000

7. SUBSTRING

The SUBSTRING function extracts a substring of specified length starting from a given position:


`SELECT SUBSTRING('MICROSOFT SQL SERVER 2000', 11, 3) as Result;

Result: SQL

8. LOWER

Convert a string to lowercase using the LOWER function:


`SELECT LOWER('MICROSOFT ASP .NET WEB HOSTING') as Result;

Result: microsoft asp .net web hosting

9. UPPER

Similarly, the UPPER function converts a string to uppercase:


`SELECT UPPER('MICROSOFT ASP .NET WEB HOSTING with SQL Database') as Result;

Result: MICROSOFT ASP .NET WEB HOSTING WITH SQL DATABASE

10. REVERSE

REVERSE reverses the characters in a string:


`SELECT REVERSE('ASP.NET') as Result;

Result: TEN.PSA

11. LTRIM

Remove leading spaces with the LTRIM function:


`SELECT LTRIM(' ASP ') as Result;

Result: ASP----- (Blanks at the right side not removed)

12. RTRIM

Remove trailing spaces with the RTRIM function:


`SELECT RTRIM(' ASP ') as Result;` 

Result: -----ASP (Blanks at the left side not removed)

13. PATINDEX

PATINDEX returns the position of the first occurrence of a specified pattern:


`SELECT PATINDEX('%RO%', 'MICROSOFT') as Result;` 

Result: 4

14. STR

Convert numeric data to character data using the STR function:


`SELECT STR(140.15, 6, 1) as Result;` 

Result: 140.2

15. ASCII

Retrieve the ASCII code value of a character:


`SELECT ASCII('A') as Result;` 

Result: 65

These string functions are powerful tools in a SQL developer’s arsenal, enabling precise manipulation and extraction of information from text data. Incorporate them wisely into your queries to enhance the versatility of your SQL skills. Happy querying!

Comments

Gaurav said…
Thanks for the awesome post!
Ka Kagwang said…
how about ignoring spaces? like "family name" to "familyname" :)
Davinder Singh said…
About ignoring spaces? like "family name" to "familyname" :)

Select Replace('family name', ' ', '');
Unknown said…
How about "FamilyName" to "Family Name"
Chandrashekhar said…
This comment has been removed by the author.
smita said…
How to find name such as its second character is a.
tolstory said…
how do i chose the middle section of a string when the desired section is surrounded by the same character "/" and the start of the string is not always at the same index...

e.g. "NWST/330/23/WT6" to "330"
and "NTW/1010/43/TY7" to "1010"
and "TYQT/99/WYT3" to "99"

I have tried so may things including your list of SQL functions, and even InStr and InStre, Mid... Please HELP! :)
????
Hari said…
This comment has been removed by the author.
Hari said…
This comment has been removed by the author.
Hari said…
To get 330 from 'NWST/330/23/WT6', use the following code:

declare @a varchar(50)
Set @a = Right('NWST/330/23/WT6', Len('NWST/330/23/WT6') - CharIndex('/', 'NWST/330/23/WT6'))

select Left(@a, charIndex('/', @a) - 1)
HarpCube said…
@Linda SELECT replace('FamilyName','Family','Family ');
HarpCube said…
This comment has been removed by the author.
HarpCube said…
This comment has been removed by the author.
DuraiPandy said…
This comment has been removed by the author.
DuraiPandy said…
Thanks Very Useful for Post.

In That Function Example Syntax Was Wrong ..Pleas Make it Correct

9. UPPER string function returns the upper case string whether the entered string has lower case letters. It takes 1 argument as string value.

Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING with SQL Database’)
Result: MICROSOFT ASP .NET WEB HOSTING WITH SQL DATABASE

It Should Be
Example:
select UPPER(‘MICROSOFT ASP .NET WEB HOSTING with SQL Database’)
Result: MICROSOFT ASP .NET WEB HOSTING WITH SQL DATABASE
Unknown said…
http://incomepart.com/ref.php?page=act/ref&invcod=113618
Mahen said…
In this string "Exhibition", how can i return count of i's in this string
Unknown said…
@Mahen Check this


Create procedure CountChar
(
@Char2bCount varchar(1),
@Inputstring varchar(100)
)
as
begin
declare @count int
declare @countend int
declare @occurance int
set @occurance=0
set @count=0
set @countend=LEN(@Inputstring)
while(@count<=@countend)
begin
if(SUBSTRING(@Inputstring,@count,1)=@Char2bCount)
begin
set @occurance=@occurance+1;
end
set @count=@count+1;
end
print 'Number of Occurance of character "'+@Char2bCount+'" in the string "'+@Inputstring+'" is '+ Cast(@Occurance as varchar)
end

exec CountChar 's','SudipGuti'
Unknown said…
This will work to pull 1st instance of the string within the '\'..


DECLARE @S VARCHAR(100)
SET @S = 'MICRO\SOFT\SQL\SERVER 2000'
SELECT SUBSTRING(@S,CHARINDEX('\',@S) + 1,CHARINDEX('\',@S, CHARINDEX('\',@S) + 1 ) - CHARINDEX('\',@S) -1)

Unknown said…
how do Replacing first occurrence of certain characters in sql 2008 database
Unknown said…
how to get the initials of the strings
eg microsoft sql server
result: mss
Unknown said…
@tolstory

you can try this
select right(left('NWST/330/23/WT6', 8 ),3)
to get 330 out of the string..

Popular posts from this blog

Check If Temporary Table Exists

Multiple NULL values in a Unique index in SQL

Row To Column