Query HELP
-
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.
-
Try this:
select substring(@string, 1, patindex(@string, '% %') - 1), substring(@string, patindex(@string, '% %') - 1, len(@string) - patindex(@string, '% %'))
-
Oops, meant this:
select substring(@string, 1, patindex(@string, '% %') - 1), substring(@string, patindex(@string, '% %') + 1, len(@string) - patindex(@string, '% %'))
-
it does not work any suggestions
-
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".
-
Try replacing the variable @string with the column you are trying to split into 2 seperate columns.
-
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
-
Try this:
insert
into B ([ID],[Name])
select
left(firm,charindex(' ',firm,1)),right(firm,len(firm)-charindex(' ',firm,1)) from A
-mr
-
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply