Avatar of rikleo2001
rikleo2001

asked on 

SQL Server 2000 Split Process Issues

Hello Experts
I am using Following procedure to split two strings which are contacted by (VBCRLF), I have tried using all possible options but didn't get any solution.

dECLARE @Data1 nvarchar(100)
Declare @Cnt int
DECLARE @RowData nvarchar(2000)
DECLARE @SplitOn nvarchar(5)
SET @RowData = 'meter(a) pulsesKWh(800)'
SET @SplitOn = CHAR(13) + CHAR(10)
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin

Select Data1 = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Select Data1 = ltrim(rtrim(@RowData))

GO

Here is the result what I am getting.
meter(a) pulsesKWh(800)

I need them like below, because they are concatinated with vbcrlf means (CHAR(13) AND CHAR(10))
meter(a)
pulsesKWh(800)

Any ideas to solve this?

Thanks in advance
Microsoft SQL Server

Avatar of undefined
Last Comment
rikleo2001
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of rikleo2001
rikleo2001

ASKER

Yes, I know this work fine with that. and it spilts them without any issues.
But I am dealing with data which in SQL Server, which got imported into system using VBCRLF, and when I view it in Enterprize manager, it shows something like this.
meter(a)◙pulsesKWh(800)  (It displays a Empty squre box)

I tried to use replace as well, but it looks like SQL Server is not recognizing VBCRLF.

I really need to get this sorted, please any more help or ideas?

Thanks



Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

ok then your vbcrlf has been substituted with &#9689 not chr(13)+chr(10)

change your spliton to this value
Avatar of rikleo2001
rikleo2001

ASKER

Thanks man I tried this but still didn't work.

dECLARE @Data1 nvarchar(100)
Declare @Cnt int
DECLARE @RowData nvarchar(2000)
DECLARE @SplitOn nvarchar(6)
SET @RowData = 'meter(Abc)
pulsesKWh(800)
pulses(54)
seconds(1.4)
A(50)
V(240)'
SET @SplitOn = '&#9689'
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin

Select Data1 = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Select Data1 = ltrim(rtrim(@RowData))

GO
Any further ideas?

Many Thanks
Avatar of rikleo2001
rikleo2001

ASKER

I solved it Thanks man.
I used
CHAR(32) + CHAR(13)+ CHAR(10)
And it worked.........
Thanks
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo