Selecting data from a string for a join

[Database Administrator]

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.

[Database Administrator]

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

[Database Administrator]

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
>

[Database Administrator]

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
>

[Database Administrator]

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
>

[Database Administrator]

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