Stripping field from the 1st full stop...

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

Stripping field from the 1st full stop...

Author

Topic

jjz
Starting Member

31 Posts

Posted - 2009-03-18 : 11:14:35

i need to strip a field and only use all characters before the first full stop as shown below.

how do i go about doing that.

k156int111001.ds1.ad.ab.co.za
k156int11f002.ds1.ad.ab.co.za
k156int126001.ds1.ad.ab.co.za

i am only interested in working with this:
k156int111001
k156int11f002
k156int126001

Please assist.

Thanx

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-18 : 11:17:22

SELECT LEFT (COL1, CHARINDEX('.',COL1) - 1) from urtable

jjz
Starting Member

31 Posts

Posted - 2009-03-18 : 11:25:16

i'm getting the below error msg.
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

Please explain what does the -1 do

thnx

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-18 : 11:26:02

I dint use substring at all. I used LEFT. What query did you try?

jjz
Starting Member

31 Posts

Posted - 2009-03-18 : 11:28:13

SELECT LEFT ([port hostname],CHARINDEX('.',[port hostname]) - 1)
from z_Switch_Ports

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-18 : 11:33:15

declare @t table (port_hostname varchar(100))
insert @t
select 'k156int111001.ds1.ad.ab.co.za' union all
select 'k156int11f002.ds1.ad.ab.co.za' union all
select 'k156int126001.ds1.ad.ab.co.za'

SELECT LEFT ([port_hostname],CHARINDEX('.',[port_hostname]) - 1)
from @t

Result
-------
k156int111001
k156int11f002
k156int126001

Are you sure this is the only thing you are doing?

jjz
Starting Member

31 Posts

Posted - 2009-03-18 : 11:42:28

tried this way and it worked, however it is not helping because the field [port name]i am getting it from a table and it is rather too big for me to manually select each record as per your example.

is there a way that i can do this but getting port name from the table as per my initial example.

thx

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-18 : 12:05:55

Probably you are getting the error if you hit a value that has no . in it. The CHARINDEX returns 0 and you try to take the LEFT part with a param of -1.

CASE will work


SELECT
    CASE
        WHEN [port hostname] LIKE '%.%' THEN LEFT ([port hostname],CHARINDEX('.',[port hostname]) - 1)
        ELSE [port hostname]
    END AS [Port Hostname]
FROM
    z_Switch_Ports

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jjz
Starting Member

31 Posts

Posted - 2009-03-19 : 01:42:14

perfectly...thanx a mil

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-03-19 : 04:57:32

SELECT LEFT ([port hostname]+'.',CHARINDEX('.',[port hostname]+'.') - 1) AS [Port Hostname]
FROM z_Switch_Ports

Jai Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources