CODEWITHSUNDEEP
sqlsql-server
graphene
graphene

Reputation: 157

To get the first word before the first delimiter

I am using SQL Server 2012.

I need to update a column to get only the word before the first dot. Code has nvarchar as data type.

Example - Table_A

Id   Code
1    TXT.yut.get
2    yep.no
3    gT.YU.iop
4    gip

I need to get this after the update

Id   Code
1    txt
2    yep
3    gt
4    gip

I have tried with T-SQL: 

Update table_A
SET Code=lower(SUBSTRING(Code, 1, CHARINDEX('.',Code) - 1))

I get this error:

Invalid length parameter passed to the LEFT or SUBSTRING function.

It seems it can be related with 'invisible' spaces? How to overcome it? Thanks.

Upvotes: 3

Views: 911

Answers (4)

sanjaya
sanjaya

Reputation: 222

SELECT LEFT(Code, LOCATE(".", Code)-1) AS left FROM table_A

Upvotes: 1

iSR5
iSR5

Reputation: 3498

All above answers will get the job done, I'll also include two different approaches as well :

With CASE 

UPDATE Table_A
SET 
    Code = CASE WHEN CHARINDEX('.', Code) > 0 THEN LEFT(Code, CHARINDEX('.', Code) - 1) ELSE Code END

and also this Without CASE: 

UPDATE Table_A
SET 
    Code = LEFT(Code, CHARINDEX('.', Code) - 1) 
WHERE 
    CHARINDEX('.', Code) > 0

The only different here that will only grab the first dot from each row. Unlike the LIKE %.% which will cover all dots on each row. If there is not a dot, the result of CHARINDEX('.', Code) will be zero.

Upvotes: 2

Nikhil
Nikhil

Reputation: 3950

Update table_A
SET Code=substr(Code,1,instr(Code,'.',1)-1)
where Code like '%.%'

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Include WHERE clause :

UPDATE table_A
     SET Code = LOWER(SUBSTRING(Code, 1, CHARINDEX('.',Code) - 1))
WHERE Code like '%.%';

Upvotes: 3

Related Questions