Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - CHARINDEX() and SUBSTRING() - Locating and Taking Substrings

By: FYIcenter.com

(Continued from previous topic...)

How To Locate and Take Substrings with CHARINDEX() and SUBSTRING() Functions?

Transact-SQL is not a language designed for manipulating strings, but it does have two simple functions to locate and take substrings: CHARINDEX() and SUBSTRING().

The tutorial exercise below assumes two given strings: 'Pages: 18' and 'Words: 3240'. The objective is to calculate the number of words per page. Read the script below to see how this is done by using CHARINDEX() and SUBSTRING() functions:

DECLARE @sPages VARCHAR(40), @sWords VARCHAR(40);
SET @sPages = 'Pages: 18';
SET @sWords = 'Words: 3240';
SET @sPages = SUBSTRING(@sPages, CHARINDEX(':', @sPages)+1, 20); 
SET @sWords = SUBSTRING(@sWords, CHARINDEX(':', @sWords)+1, 20); 
PRINT 'Number of words per page: '
   + CONVERT(VARCHAR(20), CONVERT(INT, @sWords)/CONVERT(INT, @sPages));
GO
Number of words per page: 180

If you are a PHP developer, you can get this done in a much quick way.

(Continued on next topic...)

1. How To Concatenate Two Character Strings Together?
2. What Happens When Unicode Strings Concatenate with Non-Unicode Strings?
3. How To Convert a Unicode Strings to Non-Unicode Strings?
4. What Are the Character String Functions Supported by SQL Server 2005?
5. How To Insert New Line Characters into Strings?
6. How To Locate and Take Substrings with CHARINDEX() and SUBSTRING() Functions?
7. How To Concatenate Two Binary Strings Together?
8. Can Binary Strings Be Used in Arithmetical Operations?
9. How To Convert Binary Strings into Integers?
10. Can Binary Strings Be Converted into NUMERIC or FLOAT Data Types?
11. Can Binary Strings Be Converted into Character Strings?
12. Can Binary Strings Be Converted into Unicode Character Strings?
13. How To Convert Binary Strings into Hexadecimal Character Strings
14. What Are Bitwise Operations?