SQL for CONCAT and remove character from certain columns
Posted by:
manik safo
Date: March 13, 2018 02:13PM
Hi
I need help with a SQL and was hoping to get some guidance, I am completely new to this and currently I am stuck on this, I can basically write the statements by them self but when I try to make a statement covering all I want I get stuck.
statement 1:
SELECT project_no, doc_title, doc_type, CONCAT("BD/",doc_no) FROM client_document AS temp_table
statement 2:
SELECT project_no,doc_no,doc_title,doc_type ,
CASE
WHEN doc_type = "A01" THEN doc_no=LEFT(doc_no, LENGTH(doc_no)-4)
END
FROM client_document
on the last statement its not working as intended though, I just get a new column in return with 0 in for A01 doc types... instead of it removing last 4 character from doc no with doc type A01.
What I am trying to achieve is to create a new table that will:
Add CR/ to the beginning of ALL doc no , and also REMOVE the last 4 characters from all document numbers with TYPE A01, and if possible also just show documents in a specific project number