Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 22nd, 2007, 10:45 AM
Authorized User
 
Join Date: Mar 2007
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Extract part of String

Hi,
I have a table with a column for region names. Region Names are in 2 formats basically - "NAME-BU*RM" OR "NAME*RM".
I want to extract just "Name" from this string.
Can anyone advise what the query/SQL statement would look like?

Reply With Quote
  #2 (permalink)  
Old August 22nd, 2007, 11:36 PM
Authorized User
Points: 129, Level: 2
Points: 129, Level: 2 Points: 129, Level: 2 Points: 129, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2007
Location: Gurgaon, Haryana, India.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to vksingh24
Default

You can use the following sql query

SELECT LEFT(region,(INSTR(region,"*")-1))FROM MYTABLE;

__________________
Vikash Kumar Singh
Reply With Quote
  #3 (permalink)  
Old August 23rd, 2007, 02:36 PM
Authorized User
 
Join Date: Mar 2007
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Vikash,

Thanks for your response however its giving me an error that "Instr" is not a recognized function. Anyhow I wrote a query using parse function as below and it works fine.
declare @test table
( regionName varchar(40)
)
insert into @test
select RegionName from [Guidant Field Force Table]
select * from @test
select regionName,
     isnull( parsename( replace(regionName,'-','.'),2),
             parsename( replace(regionName,'*','.'),2))
     as firstPart
from @test

The problem I'm having is that there is another column named "ID" in the table that should be linked to the new "firstpart". So can you suggest any way to link the original column "ID" to this query result?

So basically original "Guidant field force table" is:

ID RegionName
289 SIERRA NEVADA-CRM*CORDERO
482 CARDIAC NW REGION* TREBBE
US20 SOUTH BAY-CRM*HENKHAUS
UF40 INLAND EMPIRE-CRM*LIPKOWSI

And my result should look like:

ID RegionName
289 SIERRA NEVADA
482 CARDIAC NW REGION
US20 SOUTH BAY
UF40 INLAND EMPIRE

So I'm able to achieve the correct result for column "RegionName" using my query but I don't know how to get the associated ID no. as well. Please advise
Reply With Quote
  #4 (permalink)  
Old August 23rd, 2007, 11:10 PM
Authorized User
Points: 129, Level: 2
Points: 129, Level: 2 Points: 129, Level: 2 Points: 129, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2007
Location: Gurgaon, Haryana, India.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to vksingh24
Default

Instr function is applicable in MS-Access.

I have tested this query in MS-Access. but i guess your are using T-SQL

__________________
Vikash Kumar Singh
Reply With Quote
  #5 (permalink)  
Old August 23rd, 2007, 11:17 PM
Authorized User
Points: 129, Level: 2
Points: 129, Level: 2 Points: 129, Level: 2 Points: 129, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2007
Location: Gurgaon, Haryana, India.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to vksingh24
Default

CHARINDEX

__________________
Vikash Kumar Singh
Reply With Quote
  #6 (permalink)  
Old August 23rd, 2007, 11:18 PM
Authorized User
Points: 129, Level: 2
Points: 129, Level: 2 Points: 129, Level: 2 Points: 129, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2007
Location: Gurgaon, Haryana, India.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to vksingh24
Default

Use this sql query if you are using T-SQL

SELECT ID, LEFT(region,(CHARINDEX(region,"*")-1))FROM MYTABLE;

__________________
Vikash Kumar Singh
Reply With Quote
  #7 (permalink)  
Old August 24th, 2007, 10:04 AM
Authorized User
 
Join Date: Mar 2007
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Vikash,

You're right. I'm using T-SQL.
I used the query:
SELECT ID,LEFT(regionname,(CHARINDEX('*', regionname)-1))
FROM MyTable;

The output I get is:

ID RegionName
289 SIERRA NEVADA-CRM
482 CARDIAC NW REGION
US20 SOUTH BAY-CRM
UF40 INLAND EMPIRE-CRM

So this is not the exact output.

The query I mentioned previously using parsename does give me the correct output. All i need to modify in that query is add column "ID". You have any thoughts on modifying tht to add anthr column?
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract first numeric string blkskullwork Javascript 1 November 6th, 2006 02:37 AM
how to extract from a string fraijo SQL Server 2000 3 October 25th, 2006 09:55 PM
Getting Part of a String needelp Access VBA 4 September 19th, 2006 06:22 AM
Extract part of field from inconsistent data Roly Reefer Access VBA 4 March 31st, 2005 09:25 AM
A query to extract part of a string RayL Access 3 March 10th, 2004 09:42 AM



All times are GMT -4. The time now is 08:27 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.