Remove everything after first space – SQL query

[Ok ok, I know this blog is not mainly about coding but the amount of time I spent on this SQL query, I figured it needs some kind of recognition and I figured I can’t be the only one so thought of sharing.]

So bare with me, if you are not into coding, and if you are, this query may be simple, but for me I feel triumphant as I managed to do this by myself. (Of cause with online reading, and feel free to use this query in codings.)

Before getting to the query let me explain the scenario.

You have a table with list of names of Manufactures and may be some manufacture’s name may contain 2 ports. (Ex. XXX International). So if you want just to get the first name (XXX) you can use the query below.

[NOTE: these coding if MS SQL, I’m sure with a little bit of tweaking you can make to work for your Dbase.]

{—–

SELECT DISTINCT left(Manufacturer,PATINDEX(‘% %’, RTRIM(LTRIM(Manufacturer)))) as FirstName

FROM tbl

UNION

SELECT DISTINCT Manufacturer

FROM tbl

WHERE PATINDEX(‘% %’, RTRIM(LTRIM(Manufacturer))) = ‘0’

—–}

First part, trims left and right of ‘Manufacturer’ (to make sure not leading and trailing spaces) then gets the position where the first space is(that’s what PATINDEX does in MSSQL, I think in some dBases it’s called POSITION, check it out) and then get the string LEFT to the first space. [The reason why I fell in love with this query is coz, it’s universal i.e. this query works no matter how long the first name is]

What’s interesting about the first part was, if the Manufacturer only has a one name, then first part of the query will NOT return them. So that’s where second part come into play.

Second part, get’s all Manufactures missed in the first part i.e. All Manufacturers with only one name. That’s why I’m making PATINDEX = 0, coz for Manufacturers with only name PATINDEX is 0.

So a simple UNION command, unions and give me the results I wanted.

Please comment and suggest if you think you know a better way of achieving this[I’m sure there is] and like I said before feel free to use this in your codings.

Advertisements
Remove everything after first space – SQL query

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s