Share via

Paste a whole list, each row between two characters, at once

Ivann38 1 Reputation point
2021-07-06T17:29:20.323+00:00

So, I have a select list where I select names from a database.

I catch them (the names) from a list, but I need them to be pasted between this characters '%' since I'm selecting results using a like statment that goes like this:

select reason from company where SUBSTRING(reason,3,50) like '1%'
or SUBSTRING(reason,3,50) like '2%'
or SUBSTRING(reason,3,50) like '3%'

The problem is that I'm pasting one by one, and it is taking a lot of time.

Is there a way of pasting everything into a query between this '%' at once?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Michael Taylor 51,346 Reputation points
    2021-07-06T17:36:55.293+00:00

    Your query doesn't make a lot of sense to me. You are using substring to select a subset of a string from your reason column and then you're using like on it to find a number?? I think you might do better using patindex or charindex or something if you need to do simple pattern matching. Providing some sample inputs and expected outputs would be helpful.

    Also please clarify what you mean by "between this %"? Do you mean you don't want to add substring calls for the remaining OR conditions?

    0 comments
  2. EchoLiu-MSFT 14,581 Reputation points
    2021-07-07T03:09:19.663+00:00

    Hi @Ivann38 ，

    Welcome to the microsoft tsql Q&A forum!

    Sorry, your description is not clear enough for me.For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data,We also need to see the expected result of the sample.

    If you want to find multiple fields at the same time, please refer to: 

    select reason,reason1   
from company   
where SUBSTRING(reason,3,50) like '1%'  
or SUBSTRING(reason,3,50) like '2%'  
or SUBSTRING(reason,3,50) like '3%'  
or SUBSTRING(reason1,3,50) like '1%'  
or SUBSTRING(reason1,3,50) like '1%'  
or SUBSTRING(reason1,3,50) like '1%'

    If you want to combine the conditions behind where, please refer to: 

    select reason  
from company   
where SUBSTRING(reason,3,50) like '[1-3]%'

    You can replace [1-3] in the above statement with the range you want.

    If you have any question, please feel free to let me know.

    Regards
    Echo

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments