asked on 7/5/2015

Search for part of record

All,

I need your assistance with a query.

I have a simple query that shows me all records for a specific sales id, however, some changes have been made to the sales_id. The sales_id used to be something like this 4562, now it has change to something RTUBFGH-4562-10-896001455-AUST-7895663. The sales_id is the number between the first and second dash 4562. The first part of the sales_id is not always RTUBFGH, it changes according to requirements.

How can I modify the below query so it show me the records for sales_id 4562?

Select * from sales
Where sales_id = ‘4562’

Programming Microsoft SQL Server 2005 Microsoft SQL Server 2008 SQL

Last Comment

Anoo S Pillai

7/6/2015

Mike Eghtebas

7/5/2015

Select * from sales
Where sales_id like '%4562%’

Revised...

ASKER CERTIFIED SOLUTION

Anoo S Pillai

7/6/2015

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Anoo S Pillai

7/6/2015

Another method using LEFT and STUFF

SELECT * 
FROM  sales
WHERE STUFF( LEFT(sales_id , CHARINDEX('-', sales_id  , CHARINDEX('-', sales_id ) + 1) - 1),
              1,
              CHARINDEX('-', sales_id ) , 
	      '') = '4562'

Open in new window

The logic is to extract characters till second '-' from the LEFT and replace characters till first '-' ( including first '-') with a blank character ( '' ) . This may work faster than the previous one if your table is large.

Anoo S Pillai

7/6/2015

@eghtebas -
"Select * from sales Where sales_id like '%4562%’ " could have a slight problem in this case, It doesn't select specifically for characters between first and second '-' . It would select the rows even if the string 4562 comes anywhere else.

Say for example, all the following rows would be selected if like '%4562%’ is used. ( I think that is no the intended behavior )

RTUBFGH-4562-10-896001455-AUST-7895663
4562FGH-1234-10-896001455-AUST-7895663
RTUBFGH-1234-10-896004562-AUST-7895663
RTUBFGH-4562-10-896001455-AUST-7894562

jose11au

7/6/2015

ASKER

Thank you so much. This is great!

Mike Eghtebas

7/6/2015

@Anoo,

Thank you for good explanation. In that case, the following may be a simpler solution:

Select * from sales
Where sales_id like '%-4562-%’

Open in new window

Anoo S Pillai

7/6/2015

@eghtebas - Yours is a pretty simple solution and may perfectly fit in this case.

But, unless and otherwise we know the string pattern of sales_id column, the validity of clause " sales_id like '%-4562-%’ " cannot be verified. I assume that the four digit value 4562 is given for an illustration.

If the two digit value 10 is a permitted in place of 4562, then sales_id would be
RTUBFGH-10-10-896001455-AUST-7895663

OR If a big number 896001455 is a permitted in place of 4562, then sales_id would be
RTUBFGH-896001455-10-896001455-AUST-7895663

OR even If 4562 is a valid value in place of AUST, then sales_id would be
RTUBFGH-4562-10-896001455-4562-7895663

Am I thinking too pessimistic because of my experience as a DBA ?