genadian
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
Example
Field is Casenum
value is XX-200XXXX
Thanks,
Genadian
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
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.
ASKER
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
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
UPDATE [TableName]
SET [TableName].[Casenum] = IIF(MID([TableName].[Casen
MID([TableName].[Casenum],