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?

You can use the following sql query

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

__________________
Vikash Kumar Singh

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

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

CHARINDEX

__________________
Vikash Kumar Singh

Use this sql query if you are using T-SQL

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

__________________
Vikash Kumar Singh

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?