CODEWITHSUNDEEP
sqlsql-server
Ricky
Ricky

Reputation: 143

Remove first character of data output

Good Morning I need to drop NULL values and the first character of data output from a character field with the following values: NULL, A12345, A123456, A123456789, bytes between 6 - 10.

Is there a way to combine isnull and right(len concurrently? This the preferred method, given some additional data compilation that needs to occur downstream where the mycolumn - 1 will be used as a primary key.

Tried multiple variation on this without success, used string as well and that produced a syntax error

select isnull(right([my column],''len[my column]-1)  [my column]

This will remove null values 

[my column] = isnull([my column],'')

But this does not remove the 1st character 

right([my column], len([my column]-1) [my column]

Upvotes: 1

Views: 1731

Answers (3)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

Your query should be something like this : 

SELECT ISNULL(RIGHT([Value], LEN([Value])-1), ' ') AS NewValue FROM MyTable

You can check the execution from here :

SQL Fiddle

MS SQL Server 2017 Schema Setup:

 CREATE TABLE MyTable
            (ID int,
            Value varchar(max),
            )

INSERT INTO MyTable (ID,Value)VALUES(1,NULL)      
INSERT INTO MyTable (ID,Value)VALUES(2,'A12345')      
INSERT INTO MyTable (ID,Value)VALUES(3,'A123456')      
INSERT INTO MyTable (ID,Value)VALUES(4,'A123456789')

Query 1:

select ISNULL(right([Value], len([Value])-1), ' ') [Value] from MyTable

Results:

|     Value |
|-----------|
|           |
|     12345 |
|    123456 |
| 123456789 |

Upvotes: 0

Samuel Silva
Samuel Silva

Reputation: 697

Try this,

right([my column], len([my column])-1) [my column]

-1 position is wrong.

Upvotes: 1

Serkan Arslan
Serkan Arslan

Reputation: 13393

wrong parenthesis problem, this should work.

right([my column], len([my column])-1) [my column]

and also you can use STUFF function 

STUFF([my column], 1,1,'') [my column]

Upvotes: 4

Related Questions