Prev: A rather basic question I am sure...
Next: altering XML on sql server and getting information on them
From: Erland Sommarskog on 20 Jun 2010 16:36
odeddror (odeddror(a)cox.net) writes:
> select ascii(' ') as CharacterCode
>
> When I run this in SQL I'm getting Char(11) but I know it should be
> Char(13) (this symbol should be small square like ascii 255)

char(11) = Ctrl-K = Vertical tab.

I don't know why think this should be char(13), or what you expect me
to say about it. I know nada about your data.

But rather running ASCII on a literal, you should run something like


SELECT unicode(substring(@var, pos, 1))

so that the character is not mutilitated when you copy it around.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: odeddror on 20 Jun 2010 16:44
Erland,

What I'm trying is to extract the PID segment from the HL7 Message
But from some reason this function not detecting the Carriage Return at the
end of PID segment

I was able to find the starting position but because is dynamic I was
thinking I can find the next Char(13)
And read the segment but it doesn�t look like the function can read the line
properly

HL7 look like this

MSH|^~\&|GHH LAB|ELAB-3|GHH OE|BLDG4|200202150930||ORU^R01|CNTRL-3456|P|2.4
PID|||555-44-4444||EVERYWOMAN^EVE^E^^^^L|JONES|19620320|F|||153 FERNWOOD
DR.^^STATESVILLE^OH^35292||(206)3345232|(206)752-121||||AC555444444||67-A4335^OH^20030520
OBR|1|845439^GHH OE|1045813^GHH
LAB|15545^GLUCOSE|||200202150730|||||||||555-55-5555^PRIMARY^PATRICIA
P^^^^MD^^|||||||||F||||||444-44-4444^HIPPOCRATES^HOWARD H^^^^MD
OBX|1|SN|1554-5^GLUCOSE^POST 12H
CFST:MCNC:PT:SER/PLAS:QN||^182|mg/dl|70_105|H|||F

Sometimes the PID is in 3rd raw sometimes 4 also the length is dynamic too
I can't stop when the PID is ended

Thanks,

Ed Dror

"odeddror" <odeddror(a)cox.net> wrote in message
news:uuqlEIIELHA.5472(a)TK2MSFTNGP04.phx.gbl...
> Erland,
>
> select ascii(' ') as CharacterCode
>
> When I run this in SQL I'm getting Char(11) but I know it should be
> Char(13)
> (this symbol should be small square like ascii 255)
>
> Thanks,
> Ed Dror
>
>
> "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
> news:Xns9D9D9E5971199Yazorman(a)127.0.0.1...
>> Jeroen Mostert (jmostert(a)xs4all.nl) writes:
>>> As its name implies (and even then being inaccurate), the ASCII function
>>> can only determine the character code of characters in a (VAR)CHAR
>>> expression, and it can only return values in the range 0-255. For
>>> example, ASCII(N'?') yields 67, the code for capital C, because the
>>> expression is first converted to CHAR. You want UNICODE for characters
>>> that cannot be represented in a CHAR.
>>
>> Yeah, since odeddror had nvarchar, using Unicode() is a better choice,
>> although judging from what he is doing, the unexpected character is
>> probably
>> a control character.
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000:
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>

From: Erland Sommarskog on 20 Jun 2010 16:59
odeddror (odeddror(a)cox.net) writes:
> What I'm trying is to extract the PID segment from the HL7 Message But
> from some reason this function not detecting the Carriage Return at the
> end of PID segment

I don't know what an HL7 message is, and I don't know why you expect
there to be a CR at the end. Or what the PID segment is. Nor do I know
what happens with your message before it reaches SQL Server. And in the
function you posted originally, you were only looking for space characters.

I've seen that you posted a sample, but it's useless to look at since
it's mashed in news transport. You will need to put the file in an
attachment for me to be able to make some sense out it.

But why not look at the file in a binary editor yourself? This seems to
be more a problem of understanding your data, and that is something
people here will have difficulties to help you with.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: odeddror on 20 Jun 2010 17:23
Erland,

Here is the file

Thanks,
Oded Dror

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9D9DE9DD81E9Yazorman(a)127.0.0.1...
> odeddror (odeddror(a)cox.net) writes:
>> What I'm trying is to extract the PID segment from the HL7 Message But
>> from some reason this function not detecting the Carriage Return at the
>> end of PID segment
>
> I don't know what an HL7 message is, and I don't know why you expect
> there to be a CR at the end. Or what the PID segment is. Nor do I know
> what happens with your message before it reaches SQL Server. And in the
> function you posted originally, you were only looking for space
> characters.
>
> I've seen that you posted a sample, but it's useless to look at since
> it's mashed in news transport. You will need to put the file in an
> attachment for me to be able to make some sense out it.
>
> But why not look at the file in a binary editor yourself? This seems to
> be more a problem of understanding your data, and that is something
> people here will have difficulties to help you with.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
From: odeddror on 22 Jun 2010 21:11
Erland

I fix it

Declare @TextBlob varchar(max)
Declare @HLMsg nvarchar(max)
Set @TextBlob ='
MSH|^~\&|GHH LAB|ELAB-3|GHH OE|BLDG4|200202150930||ORU^R01|CNTRL-3456|P|2.4
PID|||555-44-4444||EVERYWOMAN^EVE^E^^^^L|JONES|19620320|F|||153 FERNWOOD
DR.^^STATESVILLE^OH^35292||(206)3345232|(206)752-121||||AC555444444||67-A4335^OH^20030520
OBR|1|845439^GHH OE|1045813^GHH
LAB|15545^GLUCOSE|||200202150730|||||||||555-55-5555^PRIMARY^PATRICIA
P^^^^MD^^|||||||||F||||||444-44-4444^HIPPOCRATES^HOWARD H^^^^MD
OBX|1|SN|1554-5^GLUCOSE^POST 12H
CFST:MCNC:PT:SER/PLAS:QN||^182|mg/dl|70_105|H|||F'
SET @HLMsg = SUBSTRING(@TextBlob, CHARINDEX('PID',
@TextBlob),Len(@TextBlob))
select LEFT(@HLMsg, CHARINDEX(Char(13), @HLMsg))

Here is the function
Create function [dbo].[udf_PatientLog]
(
@TextBlob varchar(max) --Hold the entire string
)
RETURNS varchar(max)
AS
BEGIN

Declare @HLMsg nvarchar(max) --This is the second string

--Get into second string only from PID
SET @HLMsg = SUBSTRING(@TextBlob, CHARINDEX('PID',
@TextBlob),Len(@TextBlob))

--Read from the PID until carriage return
SET @TextBlob = LEFT(@HLMsg, CHARINDEX(Char(13), @HLMsg))

RETURN @TextBlob
END

Thank you for your help.
Oded


"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9D9DE9DD81E9Yazorman(a)127.0.0.1...
> odeddror (odeddror(a)cox.net) writes:
>> What I'm trying is to extract the PID segment from the HL7 Message But
>> from some reason this function not detecting the Carriage Return at the
>> end of PID segment
>
> I don't know what an HL7 message is, and I don't know why you expect
> there to be a CR at the end. Or what the PID segment is. Nor do I know
> what happens with your message before it reaches SQL Server. And in the
> function you posted originally, you were only looking for space
> characters.
>
> I've seen that you posted a sample, but it's useless to look at since
> it's mashed in news transport. You will need to put the file in an
> attachment for me to be able to make some sense out it.
>
> But why not look at the file in a binary editor yourself? This seems to
> be more a problem of understanding your data, and that is something
> people here will have difficulties to help you with.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

First  |  Prev  | 
Pages: 1 2
Prev: A rather basic question I am sure...
Next: altering XML on sql server and getting information on them