Selecting data from a string for a join

Hugo,
Thanks for the help. Even at my level, I know this is a bad design. This
is a third party package and we have no real control over some of the design
shortcomings. For what we need to do though, your approach is all I need.
Thanks again!
Stan

"Hugo Kornelis" wrote:

> On Fri, 17 Oct 2008 13:27:00 -0700, Stan G. wrote:
>
> >I have a string field that has concatenated data loaded into it. I need to
> >be able to do a join using the characters at the beginning of the field that
> >ALWAYS are followed by 1 space. There may be 4, 5 or 6 characters. The
> >field looks like this:
> >"AA-1 nnnnnnnnn" or "BB-99 xxxxxxxxxx" or "CC-888 yyyyyyyyyyyyyyy". I need
> >to be able to grab that first piece before the space as the matching field on
> >a join. What is the syntax necessary to isolate that beginning portion of
> >the string so that I can end up being able to join another table on AA-1 or
> >BB-99 or CC-888?
> >Thanks for any help.
>
> Hi Stan,
>
> Note that this is a bad table design. In a relational database, each
> piece of information should be in its own column. You might, in some
> circumstances, add a computed column to represent the concatenation of
> some other columns, but the base data should be "atomic" (i.e., exactly
> one value per cell, no more).
>
> However, if you're stuck with this design you can use
> LEFT(ColumnName, CHARINDEX(' ', ColumnName) - 1)
> to extract the characters up to the first space. Replace "ColumnName"
> with the actual name of your column, and be prepared to see lousy
> performance.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
 >> Stay informed about: Selecting data from a string for a join 

Hugo,
Spoke a little too soon. I tried this technique and I see the results flash
in the query pane briefly and then get "Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function." Is this data
related in some way?
Stan


"Hugo Kornelis" wrote:

> On Fri, 17 Oct 2008 13:27:00 -0700, Stan G. wrote:
>
> >I have a string field that has concatenated data loaded into it. I need to
> >be able to do a join using the characters at the beginning of the field that
> >ALWAYS are followed by 1 space. There may be 4, 5 or 6 characters. The
> >field looks like this:
> >"AA-1 nnnnnnnnn" or "BB-99 xxxxxxxxxx" or "CC-888 yyyyyyyyyyyyyyy". I need
> >to be able to grab that first piece before the space as the matching field on
> >a join. What is the syntax necessary to isolate that beginning portion of
> >the string so that I can end up being able to join another table on AA-1 or
> >BB-99 or CC-888?
> >Thanks for any help.
>
> Hi Stan,
>
> Note that this is a bad table design. In a relational database, each
> piece of information should be in its own column. You might, in some
> circumstances, add a computed column to represent the concatenation of
> some other columns, but the base data should be "atomic" (i.e., exactly
> one value per cell, no more).
>
> However, if you're stuck with this design you can use
> LEFT(ColumnName, CHARINDEX(' ', ColumnName) - 1)
> to extract the characters up to the first space. Replace "ColumnName"
> with the actual name of your column, and be prepared to see lousy
> performance.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
 >> Stay informed about: Selecting data from a string for a join 

Looks like I needed a ',' between the ) and the -1...correct?

"Hugo Kornelis" wrote:

> On Fri, 17 Oct 2008 13:27:00 -0700, Stan G. wrote:
>
> >I have a string field that has concatenated data loaded into it. I need to
> >be able to do a join using the characters at the beginning of the field that
> >ALWAYS are followed by 1 space. There may be 4, 5 or 6 characters. The
> >field looks like this:
> >"AA-1 nnnnnnnnn" or "BB-99 xxxxxxxxxx" or "CC-888 yyyyyyyyyyyyyyy". I need
> >to be able to grab that first piece before the space as the matching field on
> >a join. What is the syntax necessary to isolate that beginning portion of
> >the string so that I can end up being able to join another table on AA-1 or
> >BB-99 or CC-888?
> >Thanks for any help.
>
> Hi Stan,
>
> Note that this is a bad table design. In a relational database, each
> piece of information should be in its own column. You might, in some
> circumstances, add a computed column to represent the concatenation of
> some other columns, but the base data should be "atomic" (i.e., exactly
> one value per cell, no more).
>
> However, if you're stuck with this design you can use
> LEFT(ColumnName, CHARINDEX(' ', ColumnName) - 1)
> to extract the characters up to the first space. Replace "ColumnName"
> with the actual name of your column, and be prepared to see lousy
> performance.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
 >> Stay informed about: Selecting data from a string for a join 

On Sat, 18 Oct 2008 06:50:00 -0700, Stan G. wrote:

>Hugo,
>Spoke a little too soon. I tried this technique and I see the results flash
>in the query pane briefly and then get "Msg 536, Level 16, State 3, Line 1
>Invalid length parameter passed to the substring function." Is this data
>related in some way?

Hi Stan,

That means that there has to be some data with no embedded spaces. The
CHARINDEX will return 0, the - 1 (that is intended to exclude the space
itself) reduces this to -1, which is indeed an invalid length parameter
for the LEFT function (which, based on the text of the error, is
obviously implemented internally as a simplified version of SUBSTRING).

Assuming you'll need the whole string if no space is present, you'll
have to use this, more complicated expression:

CASE WHEN ColumnName LIKE '% %'
THEN LEFT(ColumnName, CHARINDEX(' ', ColumnName) - 1)
ELSE ColumnName
END

(And if, for values with no embedded space, you need to use something
else, you'll have to change the ELSE part of the above CASE expression).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: Selecting data from a string for a join