charindex question

Question

Post reply

charindex question

christine 23290

Grasshopper

Points: 21
More actions
September 9, 2015 at 1:34 pm

#307599

I am executing the following query and getting the invalid length error
select distinct ico.providerspecialty, substring(ico.providerspecialty,1,(CHARINDEX('_',ico.providerspecialty)-1))
FROM [dbo].[Import_claims] ico
where ico.providerspecialty like '%_%'
I still got the error so I removed the substring to look at the data.
I executed this
select distinct ico.providerspecialty
FROM [dbo].[Import_Claims ico
where ico.providerspecialty like '%_%'
order by 1 desc
Can someone please tell me why I am getting results to do not contain the _ (underscore)?
Here are a few of records returned. The bold ones should not be there
20
19_ADVOCATE
18_SWEDISH
18_SCP
18_MED3000
18_Kelsey
18_BLOCKVISION
18_ADVOCATE
18_ADVBAR
1710086863
1699753459
Any help would be GREATLY appreciated!!!
Christine

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

The underscore is a single character wildcard.

You need to change your condition to one of these options:

select distinct specialty

FROM [dbo].[Import_ClaimData]

where specialty like '%[_]%'

order by 1 desc

select distinct specialty

FROM [dbo].[Import_ClaimData]

where specialty like '%$_%' ESCAPE '$' --Change the $ to any desired escape character.

order by 1 desc

Reference: https://msdn.microsoft.com/en-us/library/ms179859(v=sql.110).aspx

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

christine 23290 (9/9/2015)
I am executing the following query
select distinct specialty
FROM [dbo].[Import_ClaimData]
where specialty like '%_%'
order by 1 desc
Can someone please tell me why I am getting results to do not contain the _ (underscore)?
Here are a few of records returned. The bold ones should not be there
20
19_ADVOCATE
18_SWEDISH
18_SCP
18_MED3000
18_Kelsey
18_BLOCKVISION
18_ADVOCATE
18_ADVBAR
1710086863
1699753459
Any help would be GREATLY appreciated!!!
Christine

The underscore represents any single character. If you are looking for the underscore, you need to escape the character.

Hope this helps:

declare @TempTable table (DataValue varchar(32));

insert into @TempTable

values

('20'),

('19_ADVOCATE'),

('18_SWEDISH'),

('18_SCP'),

('18_MED3000'),

('18_Kelsey'),

('18_BLOCKVISION'),

('18_ADVOCATE'),

('18_ADVBAR'),

('1710086863'),

('1699753459');

select * from @TempTable;

select * from @TempTable

where DataValue like '%_%';

select * from @TempTable

where DataValue like '%!_%' escape '!';

christine 23290 Grasshopper Points: 21 More actions · Answer 3

Thank you both very much!!!!!

Both solutions worked.

That is one I won't forget for awhile.... 🙂

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

One last thing, don't order by position (ORDER BY 1), order by named column (ORDER BY ico.providerspecialty).

LoudClear SSC Eights! Points: 999 More actions · Answer 5

Lynn Pettis (9/9/2015)
One last thing, don't order by position (ORDER BY 1), order by named column (ORDER BY ico.providerspecialty).

Lynn -

I'm interested in the reasoning behind this suggestion. I agree with it, but I'm looking for material to support my position with my colleagues.

- Adam

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

deleted....poor explanation!

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/06/bad-habits-to-kick-order-by-ordinal.aspx

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

LoudClear SSC Eights! Points: 999 More actions · Answer 8

ChrisM@Work (9/10/2015)
http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/06/bad-habits-to-kick-order-by-ordinal.aspx

Yes, my argument has heretofore been

From above article: "Invariably, someone at some point is going to come in and add a column, remove a column, or change the order of the columns."

I imagined maybe there was some other support for the argument. Optimization. Performance, destruction of Whoville, etc. But I can be comfortable with the "column organization could change" argument alone.

Thanks!

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

LoudClear (9/10/2015)
ChrisM@Work (9/10/2015)
http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/06/bad-habits-to-kick-order-by-ordinal.aspx
Yes, my argument has heretofore been
From above article: "Invariably, someone at some point is going to come in and add a column, remove a column, or change the order of the columns."
I imagined maybe there was some other support for the argument. Optimization. Performance, destruction of Whoville, etc. But I can be comfortable with the "column organization could change" argument alone.
Thanks!

And remember that you can use the alias for expressions in the ORDER BY.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Luis Cazares (9/10/2015)
LoudClear (9/10/2015)
ChrisM@Work (9/10/2015)
http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/06/bad-habits-to-kick-order-by-ordinal.aspx
Yes, my argument has heretofore been
From above article: "Invariably, someone at some point is going to come in and add a column, remove a column, or change the order of the columns."
I imagined maybe there was some other support for the argument. Optimization. Performance, destruction of Whoville, etc. But I can be comfortable with the "column organization could change" argument alone.
Thanks!
And remember that you can use the alias for expressions in the ORDER BY.

Also, clarity. It is easier to understand ORDER BY t.OrderDate than ORDER BY 3.