CODEWITHSUNDEEP
sqlsql-server
Andy K
Andy K

Reputation: 5054

using charindex and ltrim

I have a column where datas are like this

 DOESTOEIVSKI ALEXANDER
JAMES JOYCE
ROBERT LUDLUM
 MURAKAMI HARUKU

Aim: Make the first string have an upper case letter and the rest lower case and the second string keep it upper case

I've done it with the query #1: 

SELECT (UPPER(LEFT(PARTPPHY.IDENTITE,1))
+LOWER(SUBSTRING( PARTPPHY.IDENTITE, 2, CHARINDEX( ' ', PARTPPHY.IDENTITE ) - 1))
+SUBSTRING(PARTPPHY.IDENTITE,CHARINDEX( ' ', PARTPPHY.IDENTITE ),DATALENGTH(PARTPPHY.IDENTITE))),PARTPPHY.IDENTITE) 
AS IDENTITE 
FROM PARTPPHY

Issue with #1 is I have that

 DOESTOEIVSKI ALEXANDER
James JOYCE
Murakami HARUKU
 ROBERT LUDLUM

I would like to have the same result for DOESTOEIVSKI or ROBERT

I tried the ltrim as you can see below for query #2:

SELECT UPPER(LEFT(LTRIM(PARTPPHY.IDENTITE),1))
+LOWER(SUBSTRING(LTRIM(PARTPPHY.IDENTITE), 2, CHARINDEX( ' ', LTRIM(PARTPPHY.IDENTITE)) - 1))
+SUBSTRING(LTRIM(PARTPPHY.IDENTITE),CHARINDEX( ' ', LTRIM(PARTPPHY.IDENTITE) ),DATALENGTH(LTRIM(PARTPPHY.IDENTITE))) 
AS IDENTITE 
FROM PARTPPHY

Issue is I have this error

Msg 537, level 16, State 2, Ligne 17 Parameter length non valid for LEFT or SUBSTRING.

Yet when I'm trying that with query #3 (see below), things are fine

SELECT UPPER(LEFT(LTRIM(' JEAN TOTOT'),1))
+LOWER(SUBSTRING(LTRIM(' JEAN TOTOT'), 2, CHARINDEX( ' ', LTRIM(' JEAN TOTOT')) - 1))
+SUBSTRING(LTRIM(' JEAN TOTOT'),CHARINDEX( ' ', LTRIM(' JEAN TOTOT') ),DATALENGTH(LTRIM(' JEAN TOTOT')))

Can anyone give me an explanation?

Thanks

Update: After Zohar's comment, I had a look at the data definition of the field. It is an identity one... I think it does make a difference but I'm not sure how or why...

DDL:

CREATE TABLE [dbo].[PARTPPHY1](
[IU_PART_PP] [int] IDENTITY(1,1) NOT NULL,
[TITRE] [int] NULL,
[NOM_NAISSANCE] [varchar](100) NULL,
[NOM_USAGE] [varchar](100) NULL,
[PRENOM] [varchar](20) NULL,
[AUTRES_PRENOMS] [varchar](60) NULL,
[IDENTITE]  AS ((([PRENOM]+' ')
+case when isnull([NOM_USAGE],'')='' 
then [NOM_NAISSANCE] else [NOM_USAGE] end)
+case when nullif([NOM_USAGE],'') IS NULL then '' else (' (NEE '+[NOM_NAISSANCE])+')' end))

DML

INSERT INTO PARTPPHY (IU_PART_PP,NOM_NAISSANCE,PRENOM)
VALUES(1,'BOUDJENAH','MICHEL')
INSERT INTO PARTPPHY ([IU_PART_PP],[NOM_NAISSANCE],[PRENOM])
VALUES(1,'','MIKE') 
INSERT INTO PARTPPHY ([IU_PART_PP],[NOM_NAISSANCE],[PRENOM])
VALUES(1,'DOE','JOHN') 
INSERT INTO PARTPPHY ([IU_PART_PP],[NOM_NAISSANCE],[PRENOM])
VALUES(1,'SMITH','STAN')
INSERT INTO PARTPPHY ([IU_PART_PP],[NOM_NAISSANCE],[PRENOM])
VALUES(1,'OPRAH','')
INSERT INTO PARTPPHY ([IU_PART_PP],[NOM_NAISSANCE],[PRENOM])
VALUES(1,'DESI','LU') 
INSERT INTO PARTPPHY ([IU_PART_PP],[NOM_NAISSANCE],[PRENOM])
VALUES(1,'JORDAN MIKE',NULL)

Upvotes: 3

Views: 501

Answers (4)

Zohar Peled
Zohar Peled

Reputation: 82504

Here is a solution that works:

SELECT  IDENTITE,
        LEFT(LTRIM(IDENTITE), 1) + 
        LOWER(SUBSTRING(LTRIM(IDENTITE), 2, CHARINDEX(' ', LTRIM(IDENTITE) + ' ') - 1)) + 
        CASE WHEN CHARINDEX(' ', LTRIM(RTRIM(IDENTITE))) > 0 THEN
            RIGHT(IDENTITE, LEN(IDENTITE) -  CHARINDEX(' ', LTRIM(IDENTITE)))
        ELSE '' END
FROM PARTPPHY
WHERE IDENTITE IS NOT NULL

Your problem was that you have data where there is no space in the middle.

Upvotes: 1

Jatin Patel
Jatin Patel

Reputation: 2104

below line is causing issue, when PRENOM has empty value CHARINDEX will give 0, so SUBSTRING has last value as -1!

CHARINDEX( ' ', LTRIM(PARTPPHY.IDENTITE)) - 1)

try this, it will check first if there are both part of the string available then use the logic for both string parts, else use other logic.

SELECT IDENTITE,
    CASE WHEN CHARINDEX( ' ', LTRIM(PARTPPHY.IDENTITE)) > 0 THEN
        UPPER(LEFT(LTRIM(PARTPPHY.IDENTITE),1))
        +LOWER(SUBSTRING(LTRIM(PARTPPHY.IDENTITE), 2, CHARINDEX( ' ', LTRIM(PARTPPHY.IDENTITE)) - 1 ))
        +SUBSTRING(LTRIM(PARTPPHY.IDENTITE),CHARINDEX( ' ', LTRIM(PARTPPHY.IDENTITE) ),DATALENGTH(LTRIM(PARTPPHY.IDENTITE))) 
    ELSE
        UPPER(LEFT(LTRIM(PARTPPHY.IDENTITE),1))
        + ISNULL(LOWER(STUFF(LTRIM(PARTPPHY.IDENTITE), 1,1, '')),'')    
    END
AS IDENTITE1
FROM PARTPPHY

Upvotes: 1

Edward
Edward

Reputation: 8606

The capitalization issue is nothing at all to do with identity or the calculated column.

You have leading space for DOESTOEIVSKI ALEXANDER and MURAKAMI HARUKU. Your formula capitalizes after the first space you find. Hence the whole name is capitalized.

This is why adding LTRIM fixed the problem.

Upvotes: 1

StackUser
StackUser

Reputation: 5398

Try like this,

SELECT upper(left(identite, 1)) + lower(substring(identite, 2, charindex(' ', identite) - 1)) + upper(substring(identite, charindex(' ', identite) + 1, len(identite))) AS identite
FROM PARTPPHY

Upvotes: 1

Related Questions