STUFF() and CHARINDEX()in SQL Server.

Pooja Patil
Nov 4 · 2 min read

1)Stuff():-

The Stuff() can be used to stuff a string into another string.

It inserts the string at a given position and deletes the number of characters specified from the original string.

Syntax:-

STUFF(string, start, length, new_string)

where,

string: Original string to be modified.

start: The given a length of characters will be deleted and a new sequence of characters will be inserted.

length: The numbers of characters to be deleted from the starting index in the original string.

new_string: The new string to be inserted in the place of deleted characters from the starting index.

Example:

  1. Stuff function inserts a string into another string at a specific location.

SELECT STUFF(‘SQL Tutorial’, 1, 3, ‘SQL Server’) result;

Delete the first three characters and insert the SQL Server String.

2) Stuff function to convert time from HHMM to HH: MM

SELECT STUFF(‘1015’, 3, 0, ‘:’) AS time_format;

convert time format

2) Charindex():-

CHARINDEX functions return the location of a substring in a string.

The search is NOT case-sensitive.

Syntax:-

CHARINDEX(substring, string [, start_location])

where,

substring: The substring that you want to find.

Its length is limited to 8,000 characters.

string: string can be a literal string, expression or column.

start_location: The location in the string where the search will start. The first location is 1.

Example:

1)Charindex function performs to a single string.

SELECT CHARINDEX(‘SQL’, ‘SQL Server CHARINDEX’) position;

2)Charindex function to perform a case-insensitive search.

SELECT CHARINDEX(‘SERVER’, ‘SQL Server CHARINDEX’ ) position;

If you are new to SQL Server start with the following must-watch video: -

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade