Selecting part of a string  
Author Message
dry





PostPosted: Wed Sep 12 11:10:01 PDT 2007 Top

SQL Server Developer >> Selecting part of a string

Hello!
I need some help with getting part of a substring. This is what I am doing:

select
SUBSTRING(Table1.Col1,0,PATINDEX('%-%',Table1.Col1)) as myString
from
Table1

this works if Col1 contains 12345-7654 - it selects 12345 like I want it to
get.
However, if Col1 has just 7654 then it returns nothing. What I would like it
to do is return 7654.
So, return everything before a - if it exists, if not return everything.

Can this be done? Please help!

LW

SQL Server135  
 
 
ML





PostPosted: Wed Sep 12 11:10:01 PDT 2007 Top

SQL Server Developer >> Selecting part of a string You can use the CASE expression:

select
myString
= case
when Table1.Col1 like '%-%'
then SUBSTRING(Table1.Col1,0,PATINDEX('%-%',Table1.Col1))
else Table1.Col1
end
from
Table1



ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
 
 
LW





PostPosted: Wed Sep 12 11:20:04 PDT 2007 Top

SQL Server Developer >> Selecting part of a string Thanks you so much ML for your quick reply. It works just like you said!

LW




> You can use the CASE expression:
>
> select
> myString
> = case
> when Table1.Col1 like '%-%'
> then SUBSTRING(Table1.Col1,0,PATINDEX('%-%',Table1.Col1))
> else Table1.Col1
> end
> from
> Table1
>
>
>
> ML
>
> ---
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
 
 
ML





PostPosted: Wed Sep 12 11:30:03 PDT 2007 Top

SQL Server Developer >> Selecting part of a string NP.
Good answers come from good questions. ;)


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
 
 
SQL





PostPosted: Wed Sep 12 11:48:00 PDT 2007 Top

SQL Server Developer >> Selecting part of a string >>Good answers come from good questions. ;)


Is that like
Good software comes from good requirements? ;-)

Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx





> NP.
> Good answers come from good questions. ;)
>
> ML
>
> ---
> Matija Lah, SQL Server MVPhttp://milambda.blogspot.com/


 
 
ML





PostPosted: Wed Sep 12 13:30:02 PDT 2007 Top

SQL Server Developer >> Selecting part of a string Eggs act Lee! :)


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
 
 
Hugo





PostPosted: Wed Sep 12 16:10:02 PDT 2007 Top

SQL Server Developer >> Selecting part of a string

>Hello!
>I need some help with getting part of a substring. This is what I am doing:
>
>select
>SUBSTRING(Table1.Col1,0,PATINDEX('%-%',Table1.Col1)) as myString
>from
>Table1
>
>this works if Col1 contains 12345-7654 - it selects 12345 like I want it to
>get.
>However, if Col1 has just 7654 then it returns nothing. What I would like it
>to do is return 7654.
>So, return everything before a - if it exists, if not return everything.
>
>Can this be done? Please help!

Hi LW,






--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis