Gerhardpet
asked on
Help with query in Access
I have some records in an Access database (using SQL Express for backend) where the ORD_ID field values start with an "E"
e.g. E000001234
I need help with a query where I can only find ORD_ID that start with an "E" and then update the E to X or something else.
Can someone help me?
e.g. E000001234
I need help with a query where I can only find ORD_ID that start with an "E" and then update the E to X or something else.
Can someone help me?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
update orders
set ORD_ID=replace([ORD_ID],"E ","X",1,1)
set ORD_ID=replace([ORD_ID],"E
UPDATE YourTable
SET ORD_ID = 'X' + SUBSTRING(ORD_ID, 2, 50)
WHERE ORD_ID LIKE 'E%'
SET ORD_ID = 'X' + SUBSTRING(ORD_ID, 2, 50)
WHERE ORD_ID LIKE 'E%'
ASKER
Oh my...many ways of doing this. Kelvin responded first so i will award the points to him.
Thank you for the help to all!
Thank you for the help to all!
>>Oh my...many ways of doing this. <<
True. However, it just depends what is your database, I assumed you were using SQL Server and not JET and provided syntax that is compliant with T-SQL (the SQL dialect used by SQL Server).
True. However, it just depends what is your database, I assumed you were using SQL Server and not JET and provided syntax that is compliant with T-SQL (the SQL dialect used by SQL Server).
SET ORD_ID = 'X' & RIGHT(ORD_ID,LEN(ORD_ID)-1
WHERE ORD_ID LIKE 'E*'