March 13, 2009 at 2:08 pm
How to convert this Left([Field3],InStr(1,[Field3]," ")-1)
and
Right([Field3],(Len([Field3])-InStr(1,[Field3]," "))) & [Field4]
Thank you
March 14, 2009 at 4:06 am
*How to convert this Left([Field3],InStr(1,[Field3]," ")-1)
LEFT([FIELD3],PATHINDEX(' ',[FIELD3])-1)
*Right([Field3],(Len([Field3])-InStr(1,[Field3]," "))) & [Field4]
RIGHT(FIELD3,LEN(LEFT([FIELD3],PATHINDEX(' ',[FIELD3])-1))+[FIELD4]
(if FIELD4 is also character)
March 14, 2009 at 4:27 am
Jo's solution should work, except that you need to change PATHINDEX to PATINDEX. And also make sure you check for the string existence getting the LEFT or RIGHT of the string. For. e.g
SELECT ( CASE WHEN PATINDEX(' ',[FIELD3]) > 0 THEN LEFT([FIELD3],PATINDEX(' ',[FIELD3])-1) ELSE '' END )
FROM SomeTable
Edit:
If you don't check the existence, you will get run time errors such as invalid value or something like that.
--Ramesh
March 16, 2009 at 9:17 am
Thank you, what about my second :
I need to convert this:
Right([Field3],(Len([Field3])-InStr(1,[Field3]," "))) & [Field4]
Joe's code:
RIGHT(FIELD3,LEN(LEFT([FIELD3],PATHINDEX(' ',[FIELD3])-1))+[FIELD4]
I did this: --(Field3 =FirstName and Field4 Last Name)
RIGHT (FIRST_NAME, LEN(LEFT (FIRST_NAME, PATINDEX(' ', FIRST_NAME) - 1)) + LAST_NAME) and it is not working,can you help me please
March 16, 2009 at 10:03 am
This should do:
RIGHT( FIRST_NAME, LEN( FIRST_NAME ) - PATINDEX( ' ', FIRST_NAME ) ) + LAST_NAME
--Ramesh
March 16, 2009 at 10:43 am
I am sorry, but this code is incorect is not giving me a right data:
This is what I need:
I have data:
First Name LastName
MARY LOU-TOBIN
I need to get all data from First Name where = '-'
Results:
First Name LastName
Mary Lou-Tobin
March 16, 2009 at 10:52 am
Well, in that case, this should do:
SELECT( CASE WHEN CHARINDEX( ' ', FIRST_NAME ) > 0 THEN SUBSTRING( FIRST_NAME, 1, CHARINDEX( ' ', FIRST_NAME + ' ' ) - 1 ) ELSE FIRST_NAME END ) AS FIRST_NAME,
( CASE WHEN CHARINDEX( ' ', FIRST_NAME ) > 0 THEN SUBSTRING( FIRST_NAME, CHARINDEX( ' ', FIRST_NAME ) + 1, 100 ) ELSE '' END ) + LAST_NAME AS LAST_NAME
FROM(
SELECT'MARY LOU-' AS FIRST_NAME, 'TOBIN' AS LAST_NAME
UNION ALL
SELECT'BRUCE F' AS FIRST_NAME, 'SPRINGSTEEN' AS LAST_NAME
UNION ALL
SELECT'PINK' AS FIRST_NAME, 'FLOYD' AS LAST_NAME
) A
[/code]
--Ramesh
March 17, 2009 at 8:47 am
Thank you very much, it worked, so just to understand how instr translates in sql?
Example:InStr(1,[last_name]," ")
March 17, 2009 at 9:22 am
Like "InStr" function, "CHARINDEX" function returns the first occurrence of the specified text within another text from a specified position. For e.g.
The syntax for the functions are:
InStr( SearchTextFromPosition , TextToSearch, SearchTextIn )
CHARINDEX( TextToSearch, SearchTextIn , SearchTextFromPosition )
--Ramesh
March 17, 2009 at 10:07 am
Thank you
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply