Using Mid(Instr()) in SQL 3

[Lost500]

Hi all,

I have a statement (that dhookom helped me with) in a query that i use in access that i need to use in SQL the statement is:

Mid([Unnamed Field], Instr([Unnamed Field],"A-")+2)

i get an error that says Instr() is not a defined function, can someone help me rewrite this so that i can use it in SQL?

Also i would like to write a simular statement to return the characters in a text field up to the "A-" value so that if i have:

abc A-123

the first statement retruns 123 in a field and the second returns abc. thanks in advance!

 

[markros]

Try

select substring([unnamed Field], charindex('A-',[Unnamed Field] + 'A-') + 2 ,len([Unnamed Field])) -- to get info after A-


--To get info for before A- try

select LEFT([unnamed Field], charindex('A-',[Unnamed Field] + 'A-') ) -- to get info before

In both cases I added A- to the field in order to avoid possible errors in the first case when you don't have A- in the field.




PluralSight Learning Library

 

[Lost500]

Thanks markros thats working but for the filed that returns theinfo before 'A-' it is leaving the A in the value so i have:

abc A-123

and its returning:

abc A

how can i only return the info up to the A, not inculding it? Thanks

 

[gmmastros]

You need to add a [!]-1[/!] into the code. If you understand the code and not just rely on copy/paste, it probably won't take too long to figure it out.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Lost500]

Thanks for the positive feedback... that's what i've been trying but since i am lacking understanding, hence the question in the first place, i'm still having trouble because in the left function there is a interger_expression which depicts how man charaters will be returned, so that's not it and in the charindex function, if the start function is negative or 0 it starts at the begining so, could you help me understand where i can use the -1 to return the values up to the 'A-'? thank for the insight

 

[markros]

Since I by mistake wrote the wrong expression for the LEFT, here is corrected version

elect substring([unnamed Field], charindex('A-',[Unnamed Field] + 'A-') + 2 ,len([Unnamed Field])) -- to get info after A-


--To get info for before A- try

select LEFT([unnamed Field], charindex('A-',[Unnamed Field] + 'A-') [red]-2[/red] ) -- to get info before

PluralSight Learning Library

 

[Lost500]

thanks markros

I was putting ,-2 that works perfect thanks for the help.

 

[gmmastros]

I'm glad you asked. It's easy to copy/paste, but more rewarding to understand.

So.... let's tear things apart to help our understanding. IN situations like this, it's best to start with the "inner most" function. In this case, it's the CharIndex function.

[tt]charindex('A-',[Unnamed Field] + 'A-')[/tt]

Charindex will return the character position of the first parameter found in the second parameter. In this case, we are looking for A-. We are looking within the [unnamed field] column. Let's take a closer look at the 2nd parameter, [unnamed field] + 'A='. What's with the + 'A-' part? Well... charindex will return 0 if the string is not found. For example....

[tt]Select CharIndex('z', 'abc') -- Returns 0[/tt]

But, if we do find the string, CharIndex returns the FIRST occurrence of it. Ex:

[tt]Select CharIndex('l', 'Yellow') -- Returns 3[/tt]

The l character appears twice, but the first occurrence is found at position #3. If we want to return the string to the left of the l, we need to use CharIndex('l','Yellow') - 1.

For example:

[tt]Select CharIndex('l','Yellow')-1 -- Returns 2[/tt]

This is great because now we can use the LEFT function to return the data to the left of the l, like this...

[tt]Select Left('Yellow', CharIndex('l','Yellow')-1) -- Returns Ye[/tt]

But... there is a potential problem here too. What if the data you are searching through does not contain the data you are searching for? We would get an error. Ex:

[tt]Select Left('Yellow', CharIndex('[!]X[/!]','Yellow')-1)
[!]Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the left function.[/!][/tt]

You see, charindex returns 0 in this case (because X is not found within Yellow). We then subtract 1 from 0 to get -1. Left('Yellow', -1) returns the error. To handle this problem, we can "force" the charindex function to always return a value. We do this by adding the search character to the end of the string, like this...

[tt]
Select Left('Yellow', CharIndex('X','Yellow' [!]+ 'X'[/!])-1) -- Returns Yellow[/tt]

Does this make sense now?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

-2 only appears to work because you have a space in the data. The -2 is secretly removing the character BEFORE the A-. Since it's a space, it appears to be doing the correct thing, but really isn't.

-1 should be used instead.

For example:

Declare @Temp VarChar(20)

Set @Temp = [!]'abc A-123'[/!]
select LEFT(@Temp, charindex('A-',@Temp + 'A-') [!]-2[/!])
-- Returns 'abc'

Set @Temp = [!]'HappyA-123'[/!]
select LEFT(@Temp, charindex('A-',@Temp + 'A-') [!]-2[/!])
-- returns 'Happ'

Set @Temp = 'HappyA-123'
select LEFT(@Temp, charindex('A-',@Temp + 'A-') [!]-1[/!])
-- Returns 'Happy'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Instead of using -2 (as in your samples you had a space before A and I didn't want to be left with extra space), the better expression as George indicated will be

select RTRIM(LEFT(Field, charindex('A-',Field + 'A-') -1))

RTRIM function will trim extra spaces at the end of the field.

PluralSight Learning Library

 

[Lost500]

thanks gmmastros i understand now, i guess my real confusion was the -1 next to the charindex function, i was putting ,-1 which would have been the interger_expression for the LEFT function which is not what i wanted. the charindex will find the position of my criteria lets say 5, which will be the number of characters the LEFT function will grab and then in my case i was just subtracting one from the amount of characters it was returning to get everything in the string before the position of the criteria?

Thanks again I'm learning... slowly

 

[gbaughma]

Kudos, George, for taking the time to educate, more than just answer. If I could give you a jumbo star, I would.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com