Feeds:
Posts
Comments

Posts Tagged ‘Get characters before space’

Assume a table having name column in the form of “First Name”
and “LastName”
 separated by space and we have to get First Name from this
column

 

SELECT CHARINDEX (  , ‘FirstName LastName‘)

SELECT
CHARINDEX (  , ‘FirstName ‘)                   /*Spacerigth
hand side*/

SELECT   CHARINDEX (  , ‘FirstName‘)   /*Nospace*/

 

Output

10

10

1

0

 ‘

Now see the detail explanation below for above cases!

 

SELECT RTRIM (LTRIM (LEFT (‘FirstName
LastName‘
,
CHARINDEX
(  , ‘FirstName LastName‘) )))

 

Output

FirstName

This is correct

 

 

SELECT RTRIM (LTRIM (LEFT (FirstName , CHARINDEX (  , FirstName ) )))

Output

FirstName

 This is correct

 

SELECT RTRIM (LTRIM(LEFT(FirstName,CHARINDEX( , FirstName) )))

Output

 This is wrong

 

No output here because CHARINDEX will
return value 0 and there are no characters before this.

So use below:

 

SELECT CASE WHEN CHARINDEX ( , NameColumn) =0

THEN NameColumn

ELSE
RTRIM (LTRIM (LEFT (NameColumn, CHARINDEX ( , NameColumn))))

END

Advertisements

Read Full Post »

%d bloggers like this: