Simple Parse? 1

[Sunwriter]

Hi All:

Environment: SQL 7, Field = nvarchar (50)

I need to parse 4 values from a string. The values will always be separated by a space, but will be of varying lenghts. Example:

90 0 329 419
126 0 132 258
114 0 14 128
104 0 18 122
342 0 193 535

Thanks,

Jeff

 

[gmmastros]

If it will always be 4 values AND seperated by a space, then you could use the parse name function to get this.

Copy/paste this to QA to see how it works.

 Declare @Temp Table (Value VarChar(50))

INsert into @Temp Values('90 0 329 419')
INsert into @Temp Values('126 0 132 258')
INsert into @Temp Values('114 0 14 128')
INsert into @Temp Values('104 0 18 122')
INsert into @Temp Values('342 0 193 535')

Select 	Value, 
		ParseName(Replace(Value, ' ','.'),4),
		ParseName(Replace(Value, ' ','.'),3),
		ParseName(Replace(Value, ' ','.'),2),
		ParseName(Replace(Value, ' ','.'),1)
From 	@Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Sunwriter]

Thank you! That will do the trick.

I actually have another very similar field that I need to parse also. The only difference is that the field could be null, empty, or any number of mostly 3 digit sets of numbers, each set separated by a space.

If you have a suggestion for this situation, it would be greatly appreciated.

Thanks,

Jeff

 

[gmmastros]

The same query applies, I believe. If the value is empty string or null, there really isnt't anything to parse, so you can check for that in your where clause.

Declare @Temp Table (Value VarChar(50))

INsert into @Temp Values('90 0 329 419')
INsert into @Temp Values('126 0 132 258')
INsert into @Temp Values('114 0 14 128')
INsert into @Temp Values('104 0 18 122')
INsert into @Temp Values('342 0 193 535')
Insert Into @Temp Values('')
Insert Into @Temp Values(NULL)

Select     Value, 
        ParseName(Replace(Value, ' ','.'),4),
        ParseName(Replace(Value, ' ','.'),3),
        ParseName(Replace(Value, ' ','.'),2),
        ParseName(Replace(Value, ' ','.'),1)
From     @Temp
Where   RTrim(IsNull(Value, '')) <> ''

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom