Solved

SQL View Question

Posted on 2007-11-29
5
163 Views
Last Modified: 2010-03-19
I have a view with a column ADVERTISER_NAME and it hold values like

Staples.com
MacMall Affiliate Advantage Network
Dell Home & Home Office
Buy.com USA and Buy.com CA

I want to define another column that should only get the characters of column ADVERTISER_NAME  until the first occurance of a space
ie if the above value are as follows:
Staples.com
MacMall
Dell
Buy.com

It is similar to the instr function in VB
0
Comment
Question by:TECH_NET
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
SQL_SERVER_DBA earned 250 total points
ID: 20376628
select substring(advertiser_name, 1, charindex(' ', Advertiser_Name)) from tablename
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20376867
This code:  select substring(advertiser_name, 1, charindex(' ', Advertiser_Name)) from tablename

 will give you a trailing blank.

Try:  select substring(advertiser_name, 1, charindex(' ', Advertiser_Name) - 1) from tablename
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20379594
Make sure you're aware that the code you've accepted will leave you with a trailing blank in your data which means you will end up with data that you can't query and find.
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20379602
cmangus, give it up...He/She read it the first time...stop reaching...
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20381987
It's not reaching.  This user will have data they can't find.  They're expecting to find "Dell" and the value stored in the table is "Dell " with a trailing space.  Those two values won't match in a query.

In my last 25+ years working on data systems this has tripped me up more than once.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question