Query the SQL query needed to trim the result
I have a table in MS Access database and need to create a query to truncate the result. For example:
here is the table:
-------------------------------------
search code | relation | environment |
-------------------------------------
Server.PRD | installs | Production |
-------------------------------------
Server.DEV | installs | Development |
-------------------------------------
The result I need to display in the query view:
---------------------------------------------------------
search code short | search code | relation | environment |
---------------------------------------------------------
Server | Server.PRD | installs | Production |
---------------------------------------------------------
Server | Server.DEV | installs | Development |
---------------------------------------------------------
I am having a hard time working out a query to display the result as stated above. So I tried to break the task down into small pieces, but now I am stuck with the first step:
I tried to turn off the "PRD" or "DEV" characters ( some of them are 4 characters like "PROD" and they are not always at the end of the search code, for example it could be "Server". PROD.DB ' ) , I ran the query:
SELECT TRIM(TRAILING 'PRD' FROM SELECT search code FROM TABLENAME)
but that doesn't seem to work. Can someone please give me some ideas to write a query to display the result?
Thanks in advance.
source to share
Try LEFT([search code], LEN([search code])-3)
EDIT . Use a .
function to search INSTR
, for example:LEFT([search code], INSTR([search code], '.') - 1)
EDIT . To handle NULL, empty string, etc:
IIF
(
(INSTR([search code], '.') = 0 OR [search code] IS NULL),
[search code],
LEFT([search code], INSTR([search code], '.') - 1)
)
Note that this does not handle more than one .
in the same value.
source to share