Instr convert from access to sql server

  • How can I convert from access to sql server

    NA: Right([Field6],Len([Field6])-InStr(1,[Field6],"Unit")+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)

  • 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?

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

  • 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...

  • 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))

  • 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

  • Thank you

  • 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]

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

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

  • 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.

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

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