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开发者_StackOverflow社区 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
UPDATE Documents
SET Account = STUFF(Account, CharIndex('-', Account)+3, 1000, '')
where SubmissionID=45925 AND Account like '%-___%'
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.
You could use a Common Table Expression if you are using SQL Server 2005 or greater.
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]
Thanks to everyone for their comments and suggestions. I used some of what was here to devise a solution that I think will work well for me. Here it is.
Update Documents
Set Acount=LEFT(Account,Len(Account)-2)
WHERE Submid=41632 AND Account LIKE '%-____'
(Thanks to @Richard for that last bit!')
精彩评论