I need help to build a query with wildcard and strings.

Question

I need help to build a query with wildcard and strings.

Ysa

Mr or Mrs. 500

Points: 553
More actions
May 3, 2019 at 6:39 pm

#3634547

Hello
I need a query to bring all the account (6 varchar) when the user type into the field the three first numbers or full account
I have account 100101, 100102, 100103, 235101,235102, When user type 100 all the 100's will be in the report.
If anyway

I was using wildcard but it didn't work. I have a parameter that fed a second parameter

select distinct
a_dep as dep_value,
RTRIM(a_dep) + ' ' + RTRIM(a_dept_desc_sh) as dep_desc
from accounts
where a_level in(@LEV) and a_dep like '%%%%%'

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Ysa wrote:

Hello I need a query to bring all the account (6 varchar) when the user type into the field the three first numbers or full account I have account 100101, 100102, 100103, 235101,235102, When user type 100 all the 100's will be in the report. If anyway I was using wildcard but it didn't work. I have a parameter that fed a second parameter select distinct a_dep as dep_value, RTRIM(a_dep) + ' ' + RTRIM(a_dept_desc_sh) as dep_desc from accounts where a_level in(@LEV) and a_dep like '%%%%%'

I'm not sure that I understand what you need. Please provide some sample data in a consumable format and expected results based on that sample data. Right now, I'm not sure if you account is stored in a_dep or a_level or somewhere else.

You should also notice that a_level IN(@LEV) is the same as a_level = @LEV. You can't use it as a multivalue string (unless it's used correctly in SSRS which you didn't mention).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eric M Russell SSC Guru Points: 125561 More actions · Answer 2

a_dep like '100%'

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

msumpter Grasshopper Points: 19 More actions · Answer 3

Eric M Russell wrote:

a_dep like '100%'

This doesn't take into account '100' being passed in as a parameter, which would then require dynamic SQL?

msumpter Grasshopper Points: 19 More actions · Answer 4

msumpter wrote:

Eric M Russell wrote:

a_dep like '100%'

adep like @LEV + '%'

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 5

When you say you want to return all the 100's are you sating only return a row if the first 3 values are 100?

so if the data looked like this

100123

100124

101111

199111

200111

would you want only rows 100123 and 100124 to be returned

or are you saying you want all the values that are between 100 and 199 so all rows except 200111 would be returned?

***The first step is always the hardest *******