Query HELP

Question

Query HELP

Best-330874

SSCrazy

Points: 2350
More actions
March 29, 2007 at 2:23 pm

#116542

Hi,
   i need help on this.I have a table A which has a column called firm which
has values like as follows
1 Adelson & Company, P.C.
2 Deborah H. Brown, CPA, LLC
3 R.E. Brown & Company
4 Bill Fraher, CPA
5 Giusti, Hingston and Company
6 Hague, Sahady & Co., CPA's, P.C.
what i want is to insert the above values in Table B in the two columns called ID and Name as follows
ID                Name
1                Adelson & Company, P.C.
2                Deborah H. Brown, CPA, LLC
3               R.E. Brown & Company
4                Bill Fraher, CPA
5               Giusti, Hingston and Company
6               Hague, Sahady & Co., CPA's, P.C.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Try this:

select substring(@string, 1, patindex(@string, '% %') - 1), substring(@string, patindex(@string, '% %') - 1, len(@string) - patindex(@string, '% %'))

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Oops, meant this:

select substring(@string, 1, patindex(@string, '% %') - 1), substring(@string, patindex(@string, '% %') + 1, len(@string) - patindex(@string, '% %'))

Best-330874 SSCrazy Points: 2350 More actions · Answer 3

Best-330874

SSCrazy

Points: 2350

March 30, 2007 at 6:43 am

#697841

it does not work any suggestions

Best-330874 SSCrazy Points: 2350 More actions · Answer 4

i get the following error message when i execute it.Any Suggestions

select

substring(@string, 1, patindex(@string, '% %') - 1),

substring

(@string, patindex(@string, '% %') + 1, len(@string) - patindex(@string, '% %'))

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@string".

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Try replacing the variable @string with the column you are trying to split into 2 seperate columns.

Bipin Kadam Right there with Babe Points: 760 More actions · Answer 6

If you have alredy designed Table B then

First you create temp table as

Create table #TempTable

( ID identity(1,1)

Name varchar(50)

)

-- This will insert data into #TempTable

insert into #TempTable(Name)

select [Name]

from TableA

-- Now #TempTable contains data as you want. Import it into requried table.

--Other wise i.e. if u haven't created tableB then Create TableB same as #TempTable

mrivera Ten Centuries Points: 1050 More actions · Answer 7

Try this:

insert

into B ([ID],[Name])

select

left(firm,charindex(' ',firm,1)),right(firm,len(firm)-charindex(' ',firm,1)) from A

-mr

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

I had my pattern and expression reversed. Also note that I had to add a single quote to the sixth string due to the single quote in the company name:

create table #MyTable (

CompanyInfo varchar(128)

)

create table #MyOtherTable (

CompanyId int,

CompanyName varchar(128)

)

go

insert into #MyTable values ('1 Adelson & Company, P.C.')

insert into #MyTable values ('2 Deborah H. Brown, CPA, LLC')

insert into #MyTable values ('3 R.E. Brown & Company')

insert into #MyTable values ('4 Bill Fraher, CPA')

insert into #MyTable values ('5 Giusti, Hingston and Company')

insert into #MyTable values ('6 Hague, Sahady & Co., CPA''s, P.C.')

go

insert into #MyOtherTable

select substring(CompanyInfo, 1, patindex('% %',CompanyInfo) - 1), substring(CompanyInfo, patindex('% %', CompanyInfo) + 1, len(CompanyInfo) - patindex('% %', CompanyInfo))

from #MyTable

go

select * from #MyTable

select * from #MyOtherTable

go

drop table #MyTable

drop table #MyOtherTable

go