SQL Server Using a Condition in a Substring

Question

I have a data field consisting of account numbers like this

16530907-00 16530907-0001 16589553-00 16589553-00 

I want to select everything that is to the right of the hyphen, then if the length of this substring is> 2, I want to update this field and set it for myself minus two extra digits to the right.

I train with the 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. I really want to create an update statement that checks the characters to the right of the hyphen if there are more than two characters, then truncate any additional characters.

Any suggestions would be appreciated. Thanks

Answer 1

Thanks to everyone for their comments and suggestions. I used some of what was here to come up with a solution that I think would work well for me. Here it is.

 Update Documents Set Acount=LEFT(Account,Len(Account)-2) WHERE Submid=41632 AND Account LIKE '%-____' 

(Thanks @Richard for this last bit! ')

Answer 2

Try the following:

 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 is ugly, but it seems to do what you want

Your update will 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 to the check without hyphenated scripts, so you will not give any explanation. However, I would recommend going with @Richards solution. It is much more beautiful.

Answer 3

You can use Common Table Expression if you are using SQL Server 2005 or higher.

 WITH CTE AS ( SELECT Account , CHARINDEX('-', Account) AS [Dash Index] , CASE WHEN CHARINDEX('-', Account)+2 > 2 THEN CHARINDEX('-', Account)+2 ELSE LEN(Account) END AS [Acceptable Length] , LEN(Account) AS [Total Length] FROM Documents ) UPDATE CTE SET Account = SUBSTRING(Account, 1, [Acceptable Length]) WHERE [Total Length] <> [Acceptable Length] 

Answer 4

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