Query for value Nth character from left

genadian
genadian used Ask the Experts™
on
I need to create a query to determine if the value of the 4th character from the left is equal to 2 then update those that are not to 2. I am new at SQL and having a hard time locating this.  Data is Access to SQL.

Example
Field is Casenum
value is XX-200XXXX

Thanks,
Genadian
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
UPDATE Tbl
SET  Casenum = CASE WHEN SUBSTRING(CaseNum, 4,1) <> '2' then STUFF(CASENUM,4,1,'2') Else Casenum END
MS SQL Server Syntax

Commented:
If updating in MS ACCESS then slight syntax difference
UPDATE [TableName]
SET [TableName].[Casenum] = IIF(MID([TableName].[Casenum],4,1) <> "2",
MID([TableName].[Casenum],1,3)+"2"+MID([TableName].[Casenum],5,LEN([TableName].[Casenum])-4),[TableName].[Casenum])

Author

Commented:
Ok I got it. I don't understand the set casenum = case unless case is an array of sorts but i will work it out. The command works great.

Thank You,
Genadian

Commented:
SET CASENUME = CASE WHEN ......
This is part of the UPDATE TABLE statement. This statement evaluates each record one by one and overwrites the value on that record based on what returns true in the CASE statement.
Glad I could help.
P.
 

Author

Commented:
pssandhu,
One more question on thisif you don't mind. What would be the correct syntax for a select statement on this. I tried some variations of the following
select casenum, officenum, dopen where Casenum = CASE WHEN SUBSTRING(CaseNum, 4,1) <> '2' and dopen > '12/31/1998'
I get the following
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '12/31/1998'.

Thanks,
Genadian

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial