Avatar of jose11au
jose11auFlag for Australia

asked on 

Search for part of record


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’
ProgrammingMicrosoft SQL Server 2005Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Anoo S Pillai
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

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

Avatar of Anoo S Pillai
Anoo S Pillai
Flag of India image

Blurred text
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
Another method using LEFT and STUFF

FROM  sales
WHERE STUFF( LEFT(sales_id , CHARINDEX('-', sales_id  , CHARINDEX('-', sales_id ) + 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.
@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 )

Avatar of jose11au
Flag of Australia image


Thank you so much. This is great!

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

@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

OR If a big number 896001455 is a permitted in place of 4562, then sales_id would be

OR even If 4562 is a valid value in place of AUST, then sales_id would be

Am I thinking too pessimistic because of my experience as a DBA ?
Microsoft SQL Server 2005
Microsoft SQL Server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo