substring - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

substring

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-02-13 : 17:25:45

Hi.I have a field with these kinds of values:

AC090B.008C21U
AC090B.008U
DC090B.006C7
ACK1610U.002
ACFRB123L.2
AVCDU.AA2300
...

how can I get rid of the last part of them(from . to the end)?
I mean I need these values:
AC090B
AC090B
DC090B
ACK1610U
ACFRB123L
AVCDU
....

thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 17:30:46

[code]SELECT SUBSTRING('COL',1 ,CHARINDEX('.','COL')-1)[/code]

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-02-13 : 17:33:26

thanks for yr reply.I've tried that But gives me this error:
SELECT SUBSTRING('COL',1 ,CHARINDEX('.','COL')-1)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 17:35:21

quote:
Originally posted by a.ashabi

thanks for yr reply.I've tried that But gives me this error:
SELECT SUBSTRING('COL',1 ,CHARINDEX('.','COL')-1)

Replace COL with your column.
and use:

SELECT SUBSTRING('COL',1 ,CHARINDEX('.','COL')-1)
from your Table

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-02-13 : 17:38:33

Im sorry I've send the query.
the error is:
Invalid lenght parameter passed to the substring function.

that was funny :P

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 17:48:03

[code]SELECT COALESCE(SUBSTRING('COL',1 ,NULLIF(CHARINDEX('.','COL')-1,-1)),COL)
from your Table[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 17:52:09

[code]SELECT COALESCE(PARSENAME(REPLACE(COL, ' ', '.'), 2),COL)[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 17:54:57

[code]SELECT COALESCE(LEFT(COL, NULLIF(CHARINDEX('.',COL) - 1, -1)),COL)[/code]

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-13 : 23:59:38

SELECT LEFT(col1+'.',CHARINDEX('.',col1+'.')-1) FROM Urtable

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 00:55:33

[code]SELECT LEFT(COL,CASE WHEN CHARINDEX('.',COL)>0 THEN CHARINDEX('.',COL) - 1 ELSE LEN(COL) END)[/code]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-14 : 04:12:42

Try this also



declare @temp table ( string varchar(32))
insert into @temp
select  'AC090B.008C21U' union all
select  'AC090B.008U' union all
select  'DC090B.006C7' union all
select  'ACK1610U.002' union all
select  'ACFRB123L.2' union all
select  'AVCDU.AA2300'


select substring(string,1,charindex('.',string)-1) as string from @temp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 04:26:54

quote:

Originally posted by Nageswar9

Try this also



declare @temp table ( string varchar(32))
insert into @temp
select  'AC090B.008C21U' union all
select  'AC090B.008U' union all
select  'DC090B.006C7' union all
select  'ACK1610U.002' union all
select  'ACFRB123L.2' union all
select  'AVCDU.AA2300'


select substring(string,1,charindex('.',string)-1) as string from @temp

this will still break when you dont have . in any of field values

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-15 : 23:10:12

Try this Once,



declare @temp table ( string varchar(32))
insert into @temp
select  'AC090B.008C21U' union all
select  'AC090B.008U' union all
select  'DC090B.006C7' union all
select  'ACK1610U.002' union all
select  'ACFRB123L.2' union all
select  'AVCDU.AA2300' union all
select	'AVCDU' union all
select  'AA2300' 

select case when charindex('.',string) = 0 
THEN string Else substring(string,1,charindex('.',string)-1) end as string from @temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources