SQL Functions LEFT RIGHT SUBSTR LENGTH CHARINDEX

DECLARE @IDX INT,
			@Count int,
			@DeleteString varchar(4000),
	@DELIMITER varchar(1),
	@TableSeparator varchar(100),
	@PrimaryKey varchar(100)
	
	set @DeleteString = ',1,2,3,4,4,5,6'
	set @DELIMITER = ','
			
	DECLARE @CommonId VARCHAR(8000),
			@SqlSting varchar(4000)
			
	SELECT @IDX = 1
	set @Count = 0
	
	IF (LEN(@DeleteString)<1) OR @DeleteString IS NULL 
		RETURN
		
	WHILE @IDX!= 0
	BEGIN
		SET @IDX = CHARINDEX(@DELIMITER,@DeleteString)
		
		print '@IDX'
		print @IDX
		
		IF @IDX!= 0
			SET @CommonId = LEFT(@DeleteString,@IDX-1)
		ELSE
			SET @CommonId = @DeleteString

print 'asdfasdf'
print @CommonId
	IF(LEN(@CommonId)> 0)
	begin
		set @SqlSting = 'delete from ' +  @TableSeparator + ' where ' + @PrimaryKey + ' = ' + @CommonId		
		--EXEC (@SqlSting)	
	end	
	
	SET @DeleteString = RIGHT(@DeleteString,LEN(@DeleteString)-@IDX)
	IF LEN(@DeleteString)= 0 
		BREAK
	END

SQL Functions LEFT RIGHT SUBSTR LENGTH CHARINDEX

Using SQLs Text and String Manipulation Functions

SQL provides a series of functions to get information about strings or text. The most useful of these, LEFT, RIGHT, SUBSTR, LENGTH and CHARINDEX are described here.

 

SQL queries return columns of data, and these usually contain text fields such as customer names, order numbers and so on. Customer names will typically not have the same format, apart from a maximum permissible length. In this article, the example used will process the strings returned from a SQL query. Two customers names from CUSTNAME have been found by the query:

“Mr. Tiger Woods”

“Mrs. Elin Woods”

The customer title, first name, and surname are all contained in one string.

SQL LEFT and RIGHT Commands

These functions return the left and right parts of a string. The syntax for the LEFT function is:

LEFT(CUSTNAME,6)

For the two customers, the leftmost 6 characters are:

“Mr. Ti”

“Mrs. E”

The space between the period and the first name counts as a character.

The RIGHT function has exactly the same syntax:

RIGHT(CUSTNAME,5)

and returns the values

“Woods”

“Woods”

SQL SUBSTR Command

The SUBSTR function is similar to the LEFT and RIGHT functions. It is used to return part of the string. In this example, the 3 characters starting from position 6 are returned:

SUBSTR(CUSTNAME, 6,3) will return

“ige”

“Eli”

It is important not to exceed the length of the entire string. The error will not be fatal, but the result may be NULL or an incomplete result. For instance,

SUBSTR(CUSTNAME, 20,3) will return NULL, since the strings are only 15 characters long, and

SUBSTR(CUSTNAME,14,10) will return “ds”, the last 2 characters, even though 10 were requested.

SQL LENGTH Command

To overcome some of the problems found when using the SUBSTR function, it is often useful to use the LENGTH function. This returns the length of the string. It is very easy to use, and the syntax is:

LENGTH(CUSTNAME)

For the two strings “Mr. Tiger Woods” and “Mrs. Elin Woods” the values returned would be:

15

15

SQL CHARINDEX Command – Find Position of Text in a String

This function is used to find the position of a character in a string. In the examples used here, the titles “Mr” and “Mrs” have different lengths, so the LEFT function is not used. To return the title of each customer, it is necessary to find the space or the period, and then return characters to the left of it.

The syntax for the CHARINDEX function to find the period in the customer name, starting at position 1 is:

CHARINDEX(“.”,CUSTNAME,1)

For the customer names shown, this returns

3

4

The titles can then be found using:

LEFT(CUSTNAME, CHARINDEX(“.”,CUSTNAME,1))

“Mr.”

“Mrs.”

SQL CHARINDEX Command – Find Position of Second Instance of Text in a String

To find the forename of each customer, a similar process may be used. The locations of the first space and second space in the string must be found. First, find the position of the first space with:

CHARINDEX(” “, CUSTNAME,1)

Then find the position of the second space in the string:

CHARINDEX(” “, CUSTNAME,1+CHARINDEX(” “, CUSTNAME,1))

i.e. search for the second space starting at the position of the first space plus 1.

The SUBSTR function can then be used to find the first name, which lies between the first and second spaces.

SQL CHARINDEX From Right of String

Sometimes it is useful to find the position of a character as measured from the right, instead of from the left. To do this, the REVERSE command may be used. For example, to find the position of the first instance of the letter “o” from the right, the following will work:

CHARINDEX(“o”,REVERSE(“Mr. Tiger Woods”),1)

This will return the value 3. If the position from the left is needed, the SQL is

LENGTH(“Mr. Tiger Woods”) – CHARINDEX(“o”,REVERSE(“Mr. Tiger Woods”),1) +1

SQL String Function Summary

After the basic SELECT FROM WHERE SQL commands have been used, it is a short step up to use some more powerful commands such as the string functions LEFT, RIGHT, SUBSTR, and CHARINDEX. These are very useful tools with which to manipulate data found in a query. They are easy to use, and with a little practice they can be used in literally limitless ways. Other ways to refine SQL queries are available at this link, and free online at many training providers.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s