Instr convert from access to sql server

Question

Post reply

Instr convert from access to sql server

Krasavita

SSC-Insane

Points: 23138
More actions
March 6, 2009 at 10:27 am

#71871

How can I convert from access to sql server
NA: Right([Field6],Len([Field6])-InStr(1,[Field6],"Unit")+1)

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

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

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 1

The direct translation is:

RIGHT([Field6], LEN([Field6]) - CHARINDEX('Unit', [Field6]) + 1)

However, the the following is likely to be slightly more efficient.

Just change the final argument of the SUBSTRING function to be the maximum length of the [Field6] field.

SUBSTRING([Field6], CHARINDEX('Unit', [Field6]), 100)

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

Thank you, so

NA: Right([Field6],Len([Field6])-InStr(1,[Field6],"Unit")+1)

is equeal to this

RIGHT([Field6], LEN([Field6]) - CHARINDEX('Unit', [Field6]) + 1)

or this

SUBSTRING([Field6], CHARINDEX('Unit', [Field6]), 100)

right?

Your recomendation to use this:SUBSTRING([Field6], CHARINDEX('Unit', [Field6]), 100) right?

Krasavita SSC-Insane Points: 23138 More actions · Answer 3

Also, how can I convert this:Len(Left([Field3],InStr(1,[Field3]," or")-1)) Thank you

DPhillips-731960 Hall of Fame Points: 3904 More actions · Answer 4

yulichka (3/6/2009)
Also, how can I convert this:Len(Left([Field3],InStr(1,[Field3]," or")-1)) Thank you

Why would you pull the LEN of a LEFT that uses a position value you will end up with in the LEN anyhow?

This:

Len(Left([Field3],InStr(1,[Field3]," or")-1))

is the same result as:

InStr(1,[Field3]," or")-1

?

So your solution would be to take the charindex of your search -1...

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 5

If you use the SUBSTRING function, just set the 3rd argument of the SUBSTRING function to be the same as the maximum number of characters that the [Field6] column can contain.

SUBSTRING([Field6], CHARINDEX('Unit', [Field6]), 100)

Also, how can I convert this:Len(Left([Field3],InStr(1,[Field3]," or")-1)) Thank you

A translation of this Access expression is:

LEN(SUBSTRING([Field3], 1, CHARINDEX(' or', [Field3]) - 1))

DPhillips-731960 Hall of Fame Points: 3904 More actions · Answer 6

andrewd.smith (3/6/2009)
...
A translation of this Access expression is:
LEN(SUBSTRING([Field3], 1, CHARINDEX(' or', [Field3]) - 1))

Or simply:

CHARINDEX(' or', [Field3]) - 1

Krasavita SSC-Insane Points: 23138 More actions · Answer 7

Krasavita

SSC-Insane

Points: 23138

March 6, 2009 at 1:16 pm

#955225

Thank you

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 8

dphillips,

Yes, you are probably right that using CHARINDEX alone may be a more exact translation of the Access behaviour. It's a while since I used Access so I can't remember how Access deals with trailing whitespace. However, the 2 expressions don't always give the same result in T-SQL because trailing whitespace is not counted by the T-SQL LEN function, as demonstrated by the following example.

DECLARE @s-2 varchar(100)

SELECT @s-2 = 'Option1 or Option2' /* NB 3 spaces between 'Option1' and 'or' */

SELECT SUBSTRING(@s, 1, CHARINDEX(' or', @s-2) - 1),

LEN(SUBSTRING(@s, 1, CHARINDEX(' or', @s-2) - 1)),

CHARINDEX(' or', @s-2) - 1

[font="Courier New"]SubField Length CharIndex

----------------------- ----------- -----------

Option1 7 9[/font]

DPhillips-731960 Hall of Fame Points: 3904 More actions · Answer 9

True. Access does not strip spaces. So it depends on desired outcome.

Krasavita SSC-Insane Points: 23138 More actions · Answer 10

I am confused, sorry, how can I put it together in the query of the SQL Server. Thank you

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 11

Yulichka,

It depends on exactly what you're trying to do, which you havent told us, but if you want the behaviour of the SQL Server expression to be as close as possible to your Access expression, then use dphillips' suggestion

CHARINDEX(' or', [Field3]) - 1

You're clearly trying to determine the length of the character string that preceeds the word 'or' in the [Field3] column. If there is a single space between 'or' and whatever precedes it then there is no issue, but if there is more than one space then the expression above (and the Access expression) will include one or more trailing spaces in the count of characters, but the following expression will return a smaller number because the LEN function in T-SQL ignores trailing spaces in the count of characters.

LEN(SUBSTRING([Field3], 1, CHARINDEX(' or', [Field3]) - 1))

For example the 2 expressions return the same value in this case where there is one space between Alpha and or:

SELECT CHARINDEX(' or', 'Alpha or Omega') - 1

--Returns 5

SELECT LEN(SUBSTRING('Alpha or Omega', 1, CHARINDEX(' or', 'Alpha or Omega') - 1))

--Returns 5

But different values in this case where there are 11 spaces between Alpha and or:

SELECT CHARINDEX(' or', 'Alpha or Omega') - 1

--Returns 15

SELECT LEN(SUBSTRING('Alpha or Omega', 1, CHARINDEX(' or', 'Alpha or Omega') - 1))

--Returns 5

Based on your data and business requirements, you'll need to determine for yourself which of these behaviours you want, or maybe it's not an issue for you because there can only ever be a single space between the word 'or' and whatever precedes it.