Link to home
Start Free TrialLog in
Avatar of genadian
genadianFlag for United States of America

asked on

Query for value Nth character from left

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
ASKER CERTIFIED SOLUTION
Avatar of pssandhu
pssandhu
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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])
Avatar of genadian

ASKER

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
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.
 
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