Parsing comma separated string inside view

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)

Parsing comma separated string inside view

Author

Topic

sirirao
Starting Member

4 Posts

Posted - 2005-07-07 : 00:33:34

I am joining 3 tables to create a view and one column in one of the tables is a comma separated string which has to be displayed as separate columns in the view. I wanted to know a noce way to do this.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-07 : 00:57:07

Did you try to create view and did you face any difficulty?
Is one of columns of the tables has comma seperated string or you want to create it?
Otherwise post table structures and your exact requirement

Madhivanan

Failing to plan is Planning to fail

sirirao
Starting Member

4 Posts

Posted - 2005-07-07 : 01:07:55

Hi,

The table structures of the two tables I am trying to join are :

CREATE TABLE dbo._VF_INSTANCE(
InstanceDBKey HarmonyIdentity NOT NULL,
DICOM_BITS_ALLOCATED int,
DICOM_BITS_STORED int,
DICOM_COLUMNS int,
DICOM_ECHO_TIME float,
DICOM_ECHO_NUMBERS nvarchar(255),
DICOM_IMAGE_DATE_TIME datetime,
DICOM_IMAGE_POSITION_PATIENT nvarchar(255),
DICOM_IMAGE_ORIENTATION_PATIENT nvarchar(255),
DICOM_PHOTOMETRIC_INTERPRETATION nvarchar(255),
DICOM_PIXEL_ASPECT_RATIO nvarchar(255),
DICOM_PIXEL_SPACING nvarchar(255),
DICOM_PLANAR_CONFIGURATION int,
DICOM_PRESENTATION_CREATOR_NAME nvarchar(255),
DICOM_PRESENTATION_DESCRIPTION nvarchar(255),
DICOM_RESCALE_INTERCEPT float,
DICOM_RESCALE_SLOPE float,
DICOM_ROWS int,
DICOM_SAMPLES_PER_PIXEL int,
DICOM_SLICE_LOCATION float,
DICOM_TEMPORAL_POSITION_IDENTIFIER int,
DICOM_TRIGGER_TIME float,
DICOM_WINDOW_CENTER nvarchar(255),
DICOM_WINDOW_WIDTH nvarchar(255),
PIIM_IMAGE_PLANE_NUMBER int,
PIIM_MR_IMAGE_CHEMICAL_SHIFT_NUMBER int,
PIIM_MR_IMAGE_DIFFUSION_B_FACTOR float,
PIIM_MR_IMAGE_DIFFUSION_DIRECTION nvarchar(255),
PIIM_MR_IMAGE_PHASE_NUMBER int,
CONSTRAINT FK_VF_INSTANCE_REFERENCES_INSTANCE FOREIGN KEY (InstanceDBKey)
REFERENCES _INSTANCE ON DELETE CASCADE
)

CREATE TABLE [_INSTANCE] (
[InstanceDBKey] [HarmonyIdentity] IDENTITY (1, 1) NOT NULL ,
[SOPInstanceUID] [nchar] (64) NOT NULL ,
[SeriesInstanceUID] [nchar] (64) NOT NULL ,
[StudyInstanceUID] [nchar] (64) NOT NULL ,
[ImageNumber] [nchar] (12) NULL ,
[SOPClassUID] [nchar] (64) NULL ,
[TransferSyntaxUID] [nchar] (64) NULL ,
[SeriesDBKey] [HarmonyIdentity] NOT NULL ,
[InstanceFileName] [nvarchar] (64) NULL ,
[ImageDescription] [nvarchar] (255) NULL ,
[ReferencedSOPInstanceUID] [nchar] (64) NULL ,
[PresentationStateLabel] [nvarchar] (64) NULL ,
[CreationDateTime] [datetime] NULL ,
[PixelOffset] [numeric](18, 0) NULL ,
[PixelLength] [numeric](18, 0) NULL ,
[ObjectType] [nvarchar] (64) NULL ,
[InstanceVersion] [timestamp] NOT NULL ,
[InstanceObjectType] [nvarchar] (64) NULL ,
[DELETED] [numeric](18, 0) NULL CONSTRAINT [DF__INSTANCE_DELETED] DEFAULT (0),
CONSTRAINT [PK_INSTANCE] PRIMARY KEY CLUSTERED
(
[InstanceDBKey]
) ON [PRIMARY] ,
CONSTRAINT [UQ_INSTANCE] UNIQUE NONCLUSTERED
(
[SOPInstanceUID],
[SeriesInstanceUID],
[StudyInstanceUID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

I would like to join these tables on the key 'InstanceDBKey'

The parameter DICOM_IMAGE_POSITION_PATIENT in _VF_INSTANCE is a comma separated string and in the view I want the fields of the string as separate coulums. I wrote a user defined function to parse it but using it in the view is giving me a problem.

Can you please let me know a nice way to solve this ?

Thanks in advance,
Siri

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-07 : 01:20:24

quote:
I wrote a user defined function to parse it but using it in the view is giving me a problem.

Post that you tried

Madhivanan

Failing to plan is Planning to fail

sirirao
Starting Member

4 Posts

Posted - 2005-07-07 : 01:26:58

Here is the user defined function :

ALTER FUNCTION dbo.SplitParameters
(
@Parameter varchar(500)
)
RETURNS
@ParsedList table
(
Param varchar(50)
)
AS
BEGIN
DECLARE @Param varchar(10), @Pos int

SET @Parameter = LTRIM(RTRIM(@Parameter))+ ','
SET @Pos = CHARINDEX(',', @Parametert, 1)

IF REPLACE(@Parameter, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Param = LTRIM(RTRIM(LEFT(@Parameter, @Pos - 1)))
IF (@Param <> '')
BEGIN
INSERT INTO @ParsedList VALUES(@Param)
END
SET @Parameter = RIGHT(@Parameter, LEN(@Parameter) - @Pos)
SET @Pos = CHARINDEX(',', @Parameter, 1)

END
END
RETURN
END
GO

But I do not know to combine these values with the view.

Siri

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources