如何进行允许两个字符串串联的查询? [英] How can I make a query that allows the concatenation of two strings?

问题描述

对不起，我的问题标题令人困惑，因为我不知道如何用一个句子来概括我的问题.

Sorry for the confusing question title, because I did not know how to summary my question in one sentence.

基本上，我想要实现的是确保用户可以在数据库中准确搜索他们想要的内容.

Basically, what I want to achieve is to ensure that the users can search what exactly they want in the database.

create table business
(post_code varchar2(8) not null,
company_name varchar2(50) not null,
code_name varchar2(58) constraint pk_business_compNo primary key,
address varchar2(100) not null);

这是一个表格，我们在其中保存有关公司的信息. post_code是公司的邮政编码 company_name是公司名称 code_name是表的主键，也是post_code和company_name的串联. address是公司地址

This is a table where we keep information about companies. post_code is the postcode of the company company_name is the company name code_name is the primary key of the table and it is also a concatenation of the post_code and company_name. address is the address of the company

然后，我创建了一个触发器来完成串联

Then, I created a trigger to complete the concatenation

CREATE OR REPLACE TRIGGER tr_uppercas
BEFORE INSERT ON business
FOR EACH ROW
BEGIN 
    IF:NEW.code_name IS NULL THEN
        SELECT replace(upper(:new.post_code),' ' ,'')||replace(upper(:new.company_name),' ','')
            INTO :NEW.code_name FROM DUAL;
    END IF;
END;
/

insert all
INTO business VALUES ('n1 9sV', 'HELLO WoRLD ltd', null, 'buckingham palace' )
SELECT * FROM DUAL;

因此，如果我插入此数据，则code_name的值应为N19SVHELLOWORLDLTD

so if I insert this data, the code_name value would be N19SVHELLOWORLDLTD

我的问题是，是否有可能通过提示用户输入company_name和post_code的值，然后将这些值连接起来，然后使这些值变为大写，进行SQL查询来搜索公司.在这种情况下，应该保证找到code_name的值以及我们期望的公司.

My question is that if it is possible to make a SQL query to search a company by prompting users to put the values for company_name and post_code and then concatenate these value and then make these values uppercase. In this case, it should be guaranteed to find the value of code_name and the company we expect.

感谢您的帮助！

推荐答案

请记住表的规范化，以避免不必要的步骤.在表中添加一个数字Pk，以便它以自动递增的顺序输入.

Keep in mind the normalization of tables, to avoid unwanted steps. Add a numerical Pk in the table so that it is fed with an auto-incremental sequence.

Create Table Business(
  post_code varchar2(8) not null,
  company_name varchar2(50) not null,
  id Number constraint pk_business_compNo primary key,
  address varchar2(100) not null
);

CREATE SEQUENCE customers_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

insert all
INTO business VALUES ('n1 9sV', 'HELLO WoRLD ltd', customers_seq.nextval, 'buckingham palace' )
SELECT * FROM DUAL;

现在，您的过滤器将是用于查找ID或另一列的公司名称

Now your filter would be company name to look up the ID or another column

Select * From Business Where (company_name like '%' Or post_code like '%' Or address Like '%' );

在您的模型中，将是这样(错误的做法):

In your model it would be like this (Bad practice):

Select  * 
From    Business 
Where   code_name Like '%'|| Param_user_company_name || '%' || Param_user_code_name || '%';

这篇关于如何进行允许两个字符串串联的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！