Solved

Help with MS SQL Right Function Using the Len Function

Posted on 2014-07-23
5
468 Views
Last Modified: 2014-07-23
Hi

I have a column in a table which is made up of reference numbers following a transaction matching process.  It's there to form part of an audit trail.  At the moment, it might look something like ", 88654, 12973, 84755".  It might also be empty (if no transactions matched).  I want to get rid of the comma and space at the beginning.  I don't really want to do this as part of the original run as it would slow it down, so thought it would be easiest to do a tidy up afterwards.

The problem is that I can't get the right function to work if I use the len function.  I've tried various, but at the moment, I've got:

UPDATE SAS_T_1213_RemAds SET remRef = RIGHT(conCatRem,LEN(conCatRem)-2) WHERE conCatRem IS NOT NULL;

I get the error message:

Msg 536, Level 16, State 4, Line 5
Invalid length parameter passed to the RIGHT function.

Can anyone help with this please?

Thanks
Sarah
0
Comment
Question by:ScuzzyJo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 21

Accepted Solution

by:
Randy Poole earned 500 total points
ID: 40214208
try this, the value may not be null, but may be a blank or under 2 characters
UPDATE SAS_T_1213_RemAds SET remRef = RIGHT(conCatRem,LEN(conCatRem)-2) WHERE Len(conCatRem+'')>2

Open in new window

0
 
LVL 25

Expert Comment

by:SStory
ID: 40214213
Try something like this:
UPDATE SAS_T_1213_RemAds 
SET remRef = SUBSTRING(remRef,3,len(remRef)-2) 
WHERE conCatRem IS NOT NULL;

Open in new window

0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40214214
Hi,

You need to check the length of conCatRem before passing it to the Right function so that if the Lengh is less then 2 Right function would not get any -Ve Value which is causing error

UPDATE SAS_T_1213_RemAds SET remRef = CASE WHEN LEN(conCatRem) >= 2 THNE (RIGHT(conCatRem,LEN(conCatRem)-2) ELSE conCatRem END  WHERE conCatRem IS NOT NULL;

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40214224
what is the defined length of that column? let's say it is 4000

test it first, always backup...
select
SUBSTRING(remRef ,3, 4000)
from SAS_T_1213_RemAds 
where remRef like ' ,%'
;

UPDATE SAS_T_1213_RemAds 
SET remRef = SUBSTRING(remRef ,3, 4000)
where remRef like ' ,%'

Open in new window

0
 

Author Closing Comment

by:ScuzzyJo
ID: 40214301
Thanks to everyone who replied.  All the answers made sense to me, but Randy got there first and his solution works perfectly :-)
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2005 running VERY slowly on new hardware 22 65
how to use ROW_NUMBER() correctly 8 44
t-sql left join 2 36
SQL Server Reports (SSRS 2014) - Reports Timing Out 11 123
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question