Exclude the data starts with ING

Question

Exclude the data starts with ING

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
December 27, 2016 at 1:26 pm

#320003

Hello,
how to write a query - to exclude the field that starts with ING
select * form table where ID NOT LIKE 'ING%' ?
I want to exclude ING from all the records?
Data - for ID looks like ING10000-11999 (ING is same for all the records but the numbers will change)

Viewing 13 posts - 1 through 12 (of 12 total)

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

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

Have you tried the query that you posted? Did it return the expected results? If not, what's the problem?

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

Chris.McCarty SSC Journeyman Points: 89 More actions · Answer 2

Hello,

how to write a query - to exclude the field that starts with ING

select * form table where ID NOT LIKE 'ING%' ?

I want to exclude ING from all the records?

Data - for ID looks like ING10000-11999 (ING is same for all the records but the numbers will change)

Does the field start with ING or the Data in the field?

So For the data you could use the Substring. Select * From ID where SubString(ID,1,3) <> 'ING"

Eric M Russell SSC Guru Points: 125562 More actions · Answer 3

mcfarlandparkway (12/27/2016)
Hello,
how to write a query - to exclude the field that starts with ING
select * form table where ID NOT LIKE 'ING%' ?
I want to exclude ING from all the records?
Data - for ID looks like ING10000-11999 (ING is same for all the records but the numbers will change)

The SELECT statement you presented as an example above should exclude any rows where the value of ID begins with 'ING'.

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

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

Chris.McCarty (12/27/2016)
Hello,
how to write a query - to exclude the field that starts with ING
select * form table where ID NOT LIKE 'ING%' ?
I want to exclude ING from all the records?
Data - for ID looks like ING10000-11999 (ING is same for all the records but the numbers will change)
Does the field start with ING or the Data in the field?
So For the data you could use the Substring. Select * From ID where SubString(ID,1,3) <> 'ING"

Hi Chris,

Welcome to the forums.

I would like to suggest that you avoid using WHERE SUBSTRING(column,1,N) in your queries as it would make the query non-SARGable.

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

drew.allen SSC Guru Points: 76996 More actions · Answer 5

Chris.McCarty (12/27/2016)
Hello,
how to write a query - to exclude the field that starts with ING
select * form table where ID NOT LIKE 'ING%' ?
I want to exclude ING from all the records?
Data - for ID looks like ING10000-11999 (ING is same for all the records but the numbers will change)
Does the field start with ING or the Data in the field?
So For the data you could use the Substring. Select * From ID where SubString(ID,1,3) <> 'ING"

There is one big problem here. You should generally avoid functions on columns in predicates (ON/WHERE clauses). LIKE is SARGable if there is no leading wildcard (as the OP had). SUBSTRING(ID, 1, 3) will NEVER be SARGable.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 6

The ID field data will show -

ING20162044285910

ING20162044285976

ING20162044285979

ING20162044285973

Now I need data -

20162044285910

20162044285976

20162044285979

20162044285973

How to remove the first 3 characters ING form this field?

select all the records where ID does not start with ING so that it will return everything.

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

Some examples based on your last post.

SELECT ID,

SUBSTRING(ID, 4, 8000),

STUFF(ID, 1, 3, ''),

CASE WHEN ID LIKE 'ING%'

THEN SUBSTRING(ID, 4, 8000)

ELSE ID END

FROM (VALUES

('ING20162044285910'),

('ING20162044285976'),

('ING20162044285979'),

('ING20162044285973'),

('20162044285555'))x(ID)

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

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 8

There is no simple statement other than substring?

like select data form table where ID not....?

Chris.McCarty SSC Journeyman Points: 89 More actions · Answer 9

Chris.McCarty

SSC Journeyman

Points: 89

December 27, 2016 at 2:22 pm

#1919782

Agreed

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 10

What do you mean by agreed?

I cannot keep these substring and stuff in where clause.

I am trying to get all the data from table where ID does not start with ING.

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 11

This didn't worked for me - Select * From table where SubString(ID,1,3) <> 'ING"

It still showing all the records that starting with ING

kaj SSCommitted Points: 1607 More actions · Answer 12

mcfarlandparkway (12/27/2016)
This didn't worked for me - Select * From table where SubString(ID,1,3) <> 'ING"
It still showing all the records that starting with ING

This indicates that your data string is not what it seems to be. There must be some "invisible" characters in there.

Try to expose them by converting the field to hex and see what is hiding:

SELECT yourField, CONVERT(varbinary(10), yourField) AS hexDisplay

FROM yourTable

The hiding characters could be control characters, like Lf, Cr, CrLf, Vt, Esc etc., but once you have seen what is hiding, you can adapt your selection criteria accordingly, e.g.

SELECT *

FROM yourTable

WHERE yourField LIKE 'IN' + CHAR(10) + 'G%'

Remember that CHAR takes a numeric value, not the hex value you saw in the hex output, so you need to convert the base 16 value to a normal base 10 value, e.g. hex 0A = CHAR(10), hex 0B = CHAR(11) etc.