Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Instr convert from access to sql server Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, March 06, 2009 10:27 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, March 06, 2014 12:31 PM Points: 1,271, Visits: 1,870
Post #670453
 Posted Friday, March 06, 2009 11:24 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, October 24, 2012 2:12 PM Points: 1,213, Visits: 3,232
 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)`
Post #670507
 Posted Friday, March 06, 2009 12:17 PM
 Ten Centuries Group: General Forum Members Last Login: Thursday, March 06, 2014 12:31 PM Points: 1,271, Visits: 1,870
 Thank you, so NA: Right([Field6],Len([Field6])-InStr(1,[Field6],"Unit")+1) is equeal to thisRIGHT([Field6], LEN([Field6]) - CHARINDEX('Unit', [Field6]) + 1)or thisSUBSTRING([Field6], CHARINDEX('Unit', [Field6]), 100) right?Your recomendation to use this:SUBSTRING([Field6], CHARINDEX('Unit', [Field6]), 100) right?
Post #670568
 Posted Friday, March 06, 2009 12:46 PM
 Ten Centuries Group: General Forum Members Last Login: Thursday, March 06, 2014 12:31 PM Points: 1,271, Visits: 1,870
 Also, how can I convert this:Len(Left([Field3],InStr(1,[Field3]," or")-1)) Thank you
Post #670590
 Posted Friday, March 06, 2009 1:07 PM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, October 24, 2012 2:12 PM Points: 1,213, Visits: 3,232
 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))`
Post #670604
 Posted Friday, March 06, 2009 1:07 PM
 Ten Centuries Group: General Forum Members Last Login: Friday, August 10, 2012 6:08 PM Points: 1,156, Visits: 801
 yulichka (3/6/2009)Also, how can I convert this:Len(Left([Field3],InStr(1,[Field3]," or")-1)) Thank youWhy 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...
Post #670605
 Posted Friday, March 06, 2009 1:10 PM
 Ten Centuries Group: General Forum Members Last Login: Friday, August 10, 2012 6:08 PM Points: 1,156, Visits: 801
 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`
Post #670610
 Posted Friday, March 06, 2009 1:16 PM
 Ten Centuries Group: General Forum Members Last Login: Thursday, March 06, 2014 12:31 PM Points: 1,271, Visits: 1,870
 Thank you
Post #670613
 Posted Friday, March 06, 2009 1:26 PM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, October 24, 2012 2:12 PM Points: 1,213, Visits: 3,232
 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 varchar(100)SELECT @s = 'Option1 or Option2' /* NB 3 spaces between 'Option1' and 'or' */SELECT SUBSTRING(@s, 1, CHARINDEX(' or', @s) - 1), LEN(SUBSTRING(@s, 1, CHARINDEX(' or', @s) - 1)), CHARINDEX(' or', @s) - 1`SubField Length CharIndex----------------------- ----------- -----------Option1 7 9
Post #670622
 Posted Friday, March 06, 2009 2:26 PM
 Ten Centuries Group: General Forum Members Last Login: Friday, August 10, 2012 6:08 PM Points: 1,156, Visits: 801
 True. Access does not strip spaces. So it depends on desired outcome.
Post #670678

 Permissions