Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Instr convert from access to sql server Expand / Collapse
Author
Message
Posted Friday, March 6, 2009 10:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:22 PM
Points: 1,279, Visits: 1,884
How can I convert from access to sql server

NA: Right([Field6],Len([Field6])-InStr(1,[Field6],"Unit")+1)
Post #670453
Posted Friday, March 6, 2009 11:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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 6, 2009 12:17 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:22 PM
Points: 1,279, Visits: 1,884
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?

Post #670568
Posted Friday, March 6, 2009 12:46 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:22 PM
Points: 1,279, Visits: 1,884
Also, how can I convert this:Len(Left([Field3],InStr(1,[Field3]," or")-1)) Thank you
Post #670590
Posted Friday, March 6, 2009 1:07 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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 6, 2009 1:07 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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 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...
Post #670605
Posted Friday, March 6, 2009 1:10 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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 6, 2009 1:16 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:22 PM
Points: 1,279, Visits: 1,884
Thank you
Post #670613
Posted Friday, March 6, 2009 1:26 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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 6, 2009 2:26 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse