CODEWITHSUNDEEP
sql
Jerry Trac
Jerry Trac

Reputation: 367

SQL Character extraction

I would like to extract everything on the right side of the "underscore". I want to get 0000. I tried 

select right('M_0000',charindex('_','M_0000')-1)

but end up with just 0. Why?

Upvotes: 1

Views: 52

Answers (2)

D Stanley
D Stanley

Reputation: 152626

You can use SUBSTRING instead of RIGHT:

select SUBSTRING('M_0000',charindex('_','M_0000')+1, LEN('M_0000'))   // start at character n + 1

Upvotes: 0

Filipe Silva
Filipe Silva

Reputation: 21657

Since you are using CHARINDEX, i assume you are using SQL-SERVER.

So in your second field you should say how many characters you want. You can achieve that by doing LEN('M_0000') - charindex('_') :

select right('M_0000',len('M_0000') - charindex('_','M_0000'))

sqlfiddle demo

You were ending up with just one 0 because charindex('_') is 1, and you are telling the RIGHT function that you want 1 char from the right.

From the docs:

RIGHT ( character_expression , integer_expression )

character_expression Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.

integer_expression Is a positive integer that specifies how many characters of character_expression will be returned. If integer_expression is negative, an error is returned. If integer_expression is type bigint and contains a large value, character_expression must be of a large data type such as varchar(max).

Upvotes: 4

Related Questions