September 12, 2014 at 2:56 am
Hi there,
I have a sql table which needs to be filled up with values from html table. I have used Parsevalues function from codeproject but modified a tad bit to change it to nvarchar(max). Russian characters are showing up as question marks when I run the code below. I checked the collation property for our database and it is set to "SQL_Latin1_General_CP1_CI_AI". I have no clue as to why I am getting question marks.
CREATE TABLE [dbo].[employeeInfo](
[CompanyFullName] [nvarchar](max) NULL,
[empID] [varchar](500) NULL,
[Address] [nvarchar](500) NULL,
[Telephone] [varchar](200) NULL,
[Fax] [varchar](200) NULL,
[InsertedDate] [varchar](50) NULL,
[EmpName] [nvarchar](max) NOT NULL
) ON [PRIMARY]
END
ALTER FUNCTION [dbo].[ParseValues]
(@String nvarchar(max), @Delimiter varchar(max) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val nvarchar(max))
AS
BEGIN
DECLARE @Value nvarchar(max)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN PATINDEX(''%''+@Delimiter+''%'',@String) >0 THEN LEFT(
@String,PATINDEX(''%''+@Delimiter+''%'',@String)-1) ELSE @String END,
@String=CASE WHEN PATINDEX(''%''+@Delimiter+''%'',@String) >0 THEN SUBSTRING(
@String,PATINDEX(''%''+@Delimiter+''%'',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END
DECLARE @data varchar(Max)
SET @data= '<table>
<tr><td>"A company"</td><td>12302005328</td><td>187406, Leningrad Region, Moscow</td><td>(613) 632-38-99</td><td></td><td>Jul 09 2012 6:02AM</td><td>Эми Беттингер </td></tr>
</table>'
insert into [dbo].[employeeInfo](CompanyFullName, empID, Address, Telephone, Fax, InsertedDate, EmpName )
select convert(nvarchar(500),[1]) as CompanyFullName,[2] empid,[3] Address,[4] Telephone,[5] Fax,[6] insertedDate, convert(nvarchar(500),[7]) as EmpName from
(
select replace(b.val,'</td>','') data,rowno,id-1 as colno from
(
select 'N'+replace( replace(a.val,'</tr>',''),'</table>','') as row,
row_number() over (order by a.id) as rowno
from dbo.parsevalues(@Data,'<tr>') a
where a.val like '<td>%'
) as a
cross apply dbo.parsevalues(row,'<td>') as b
where b.id <> 1
) as a
PIVOT
(
max(data)
FOR colno
IN (
[1],[2],[4],[5],[6],[7]
)
) PivotTable
Can anybody point me in the right direction?
Thanks
Rash
September 12, 2014 at 4:00 am
I probably posted the question without trying this.
Adding N' before the string. I tried and it works.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply