Help with CharIndex - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with CharIndex

Author

Topic

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-02-26 : 17:17:00

Guys,

I am using folloiwng query to extract first part of that string.

select substring('meter(Rx07000892) pulsesKWh(1000)',1,charindex(char(13),'meter(Rx07000892) pulsesKWh(1000)')-1)

Now space between two string is actually Carrige return
'meter(Rx07000892) pulsesKWh(1000)'

This is sample I have a string which is concatinated with carrige return of 20 differant string values, and I need to get all those values using charindex, but it doesn't seem to work.

Any Help with another option please?

Thanks

SKR

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-26 : 17:21:50

"This is sample I have a string which is concatinated with carrige return of 20 differant string values, and I need to get all those values using charindex, but it doesn't seem to work."
example please ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-26 : 17:22:51

use a split function ? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-02-26 : 17:29:09

Thanks so much man, that is really helpful.
Testing it now, will get back in case have any issues.
Thanks

SKR

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-02-26 : 17:58:48

Hi,

Here what I did.

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)

Am I doing somthing wrong?

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

SKR

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-26 : 18:23:52

but your input data does not contain char(13) and char(10)
try changing to


SET @RowData = 'meter(a)' + char(13) + char(10) + 'pulsesKWh(800)'

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-02-26 : 18:30:14

Hi,

Those records actually generated from VB script, where I used VBCRLF.
When you view them in query analyzer it displyed it as flat line, but using enterprize manager will display them with squre box.

I assume in vb (VBCRLF) = SQL (char(13) + char(10))

Any further guidelines?

SKR

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-27 : 03:00:15

I mean your script here

quote:

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

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-02-27 : 14:21:24

Yes I know that. Thanks.
I think I didn't ptresent it well. Sorry my Mistake.

Let me explain it one more time.

1. My VB application using VBCRLF to contactinate two string values
2. I want my SQL to split them on based on VBCRLF in SQL Server
3. I had used aboved mentioned process to split them.
4. No Luck

Any more ideas

Thanks

SKR

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-27 : 22:24:34

I am not sure about VBCRLF. Are you sure it is char(13) + char(10) or char(10) + char(13) ?

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-02-28 : 14:15:15

It didn't work, I tried both combination.
VBCRLF means the same for (Carriage return and line feed)
When I past my string in word, it displays proper symbol of linefeed and Carriage return.
But I cann't get it seperated using those characters.

I can send you Notepad IN WHICH it has that whole string and you can view it.

Any more help ?

Many Thanks

SKR

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources