Stripping unwanted characters from a sql select query 3

[limester]

Hi,

I have searched this forum, but I am finding it difficult to locate exactly what I am trying to do.

I have a select statement, I would like to strip some of the output of the query, for example, if I get the result

'something'

but I only want to show

'some'

in the query result. How can I do this?

Thanks!

 

[Jamfool]

select replace('yourstring','your','')

you may want to look at substring or left/right functions depending on reqs.

 

[Crowley16]

there's also charindex or patindex which may come in handy...

--------------------
Procrastinate Now!

 

[limester]

Thanks for your replies!

I tried the left and right functions but they are not suitable, at least in the way I am using them.

Here is a more accurate description of what I am trying to do:

when I do a select query I get results for a workstation hostname, with additional characters on the end. I would like to strip those off. The problem is the hostname is not always the same amount of characters. My query result looks something like this:

hostname-xxxx
hostname1-xxxx
hostname2-xxxx

there is always a hyphen followed by the same characters.

I would prefer just to have the hostname returned. Is there a function that could provide this?

Thanks!

 

[TheSQL]

SELECT LEFT(hostname,LEN(LTrim(RTrim(hostname))) - 5)
FROM wherever

The LEN function returns the length of the hostname.
LTrim and RTrim are just there to make sure that it isn't padded with spaces or anything.

Hope this helps.

 

[gmmastros]

The LTrim and RTrim are not necessary.

Declare @temp Table(hostname VarChar(20))

Insert Into @temp Values('hostname-xxxx')
Insert Into @temp Values(' hostname1-xxxx      ')
Insert Into @temp Values('      hostname2-xxxx')
Insert Into @temp Values('hostname3')

Select LEFT(hostname,LEN(hostname) - 5)
From   @Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheSQL]

Good to know. That certainly cuts out some additional code.

 

[limester]

That works like a charm gmmastros!

Thanks very much! and Thanks to everyone else that responded too!!

Cheers!

 

[SuperComputing]

OK, not meaning to hi-jack, if I am... but I have a similar situation in that I need the information to the left of the hyphen. My problem is that the length of the characters on the right of the hyphen are not the same, some don't even have hyphens.

Instead of hostnames, I have ItemNumbers i.e...

PA109-55SM
PV109
FTU101-01
SS100-12
2713143-8

Is there any way to: (I know this isn't right, but is there a similar way to do it?)

SELECT (POS = InStr(ItemNumber,-) LEFT(ItemNumber,POS)) AS ItemNumber

 

[gmmastros]

Select Left(ItemNumber, CharIndex('-', ItemNumber + '-')-1)

some don't even have hyphens.

To accommodate this, I use ItemNumber + '-' in the CharIndex function, so CharIndex will ALWAYS return a value.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Oh yeah...

I want to specifically point out the InStr does not exist in T-SQL (which you probably already know).

The InStr equivalent in T-SQL is CharIndex.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SuperComputing]

Worked great, thank you George! This eliminated an IF THEN and 4 lines of code that I was using as a work around. (This is actually a SELECT from a access database on an asp webpage, but the premise is the same).

This is the final that worked:

SELECT LEFT(ItemNumber, (InStr(ItemNumber + '-', '-' )-1)) AS ItemNumber

Thanks again!