INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Member Feedback
"...I am so glad that I found your site, it is an excellent resource and has helped me greatly..."
Geography
Where in the world do Tek-Tips members come from?
|
 Stripping unwanted characters from a sql select query
|
|
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 (IS/IT--Management) |
6 Nov 07 3:15 |
select replace('yourstring','your','')
you may want to look at substring or left/right functions depending on reqs. |
|
there's also charindex or patindex which may come in handy... --------------------
Procrastinate Now! |
|
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 (Programmer) |
6 Nov 07 21:24 |
CODESELECT 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. |
|
The LTrim and RTrim are not necessary. CODEDeclare @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 (Programmer) |
6 Nov 07 21:47 |
Good to know. That certainly cuts out some additional code. |
|
That works like a charm gmmastros!
Thanks very much! and Thanks to everyone else that responded too!!
Cheers! |
|
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
|
|
CODESelect Left(ItemNumber, CharIndex('-', ItemNumber + '-')-1) 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
|
|
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
|
|
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! |
|
|
 |
|
