SQL Server Using a condition in a Substring

Question

I have a field of data which consists of account numbers like this

16530907-00
16530907-0001

16589553-00
16589553-00

I want to select everything to the right of the hyphen then if the Length of that substring is >2 I want to Update that field and set it to itself minus the two extra digits on the right.

I am practicing with a select statement

Select SUBSTRING(Account, CHARINDEX('-'), Account)+1, LEN(Account) as test
FROM Documents
WHERE SubmissionID=45925 and LEN(test)>2

This does not work. What I really want to do is create an update statement that tests the characters to the right of the hyphen if there are more than 2 characters then truncate any extra characters .

Any suggestions would be appreciated. Thanks

Answer 1

UPDATE Documents
SET Account = STUFF(Account, CharIndex('-', Account)+3, 1000, '')
where SubmissionID=45925 AND Account like '%-___%'

Answer 2

Try this:

Select SUBSTRING(Account,0,CHARINDEX('-',Account)+3) as UpdatedAccount, Account
FROM Documents 
WHERE SubmissionID=45925 
and LEN(SUBSTRING(Account, CHARINDEX('-',Account)+1,LEN(Account)) ) > 2
AND CHARINDEX('-',Account) > 0

It's ugly but appears do do what you want

Your update would look like this:

UPDATE Documents
SET Account = SUBSTRING(Account,0,CHARINDEX('-',Account)+3)
WHERE SubmissionID=45925 
and LEN(SUBSTRING(Account, CHARINDEX('-',Account)+1,LEN(Account)) ) > 2
AND CHARINDEX('-',Account) > 0

UPDATE:

Added in a check for no hyphen scenarios so you don't undate for no reason. That said, I would recommend going with @Richards solution. It's much prettier.