Selecting the data the data - 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 2005 Forums

Transact-SQL (2005)

Selecting the data the data

Author

Topic

sudha12345
Starting Member

47 Posts

Posted - 2009-08-13 : 02:44:23

I had a data in the column of a table
as 30.0 mg/30.0 ml and i want display it in 4 cols and i wrote a query like
select
parsename(Replace(REPLACE(PARAMETERVALUE,'/','.'),' ','.'),4),
parsename(Replace(REPLACE(PARAMETERVALUE,'/','.'),' ','.'),3),
parsename(Replace(REPLACE(PARAMETERVALUE,'/','.'),' ','.'),2),
parsename(Replace(REPLACE(PARAMETERVALUE,'/','.'),' ','.'),1)
FROM DRUGLIBRARY but it is not giving any data.

can any one correct this

Sudhakar

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-13 : 02:57:36

once try this

select
parsename(Replace(REPLACE(replace('30.0 mg/30.0 ml',' ',''),'/','.'),' ','.'),4),
parsename(Replace(REPLACE(replace('30.0 mg/30.0 ml',' ',''),'/','.'),' ','.'),3),
parsename(Replace(REPLACE(replace('30.0 mg/30.0 ml',' ',''),'/','.'),' ','.'),2),
parsename(Replace(REPLACE(replace('30.0 mg/30.0 ml',' ',''),'/','.'),' ','.'),1)

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-13 : 02:58:56

or this too check

select
replace(parsename(Replace(REPLACE(replace('30.0 mg/30.0 ml',' ','-'),'/','.'),' ','.'),4),'-',' '),
replace(parsename(Replace(REPLACE(replace('30.0 mg/30.0 ml',' ','-'),'/','.'),' ','.'),3),'-',' '),
replace(parsename(Replace(REPLACE(replace('30.0 mg/30.0 ml',' ','-'),'/','.'),' ','.'),2),'-',' '),
replace(parsename(Replace(REPLACE(replace('30.0 mg/30.0 ml',' ','-'),'/','.'),' ','.'),1),'-',' ')

sudha12345
Starting Member

47 Posts

Posted - 2009-08-13 : 03:13:51

Hi, This is Executing Fine, but the data may be wrong

select TOP 10
parsename(Replace(REPLACE(replace('30.5 mg/30.5 ml'
,' ',''),'/','.'),' ','.'),4),
parsename(Replace(REPLACE(replace('30.5 mg/30.5 ml'
,' ',''),'/','.'),' ','.'),3),
parsename(Replace(REPLACE(replace('30.5 mg/30.5 ml'
,' ',''),'/','.'),' ','.'),2),
parsename(Replace(REPLACE(replace('30.5 mg/30.5 ml'
,' ',''),'/','.'),' ','.'),1)

it is giving data as 30 5mg 30 5ml as 4 Columns

but i want to display the data as 30.5 mg 30.5 ml

Can you Correct the above

Sudhakar

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-13 : 03:19:13

[CODE]
select
REPLACE(PARSENAME(REPLACE(REPLACE(REPLACE('30.5 mg/30.5 ml','.','-'),'/','.'),' ','.'),4),'-','.'),
REPLACE(PARSENAME(REPLACE(REPLACE(REPLACE('30.5 mg/30.5 ml','.','-'),'/','.'),' ','.'),3),'-','.'),
REPLACE(PARSENAME(REPLACE(REPLACE(REPLACE('30.5 mg/30.5 ml','.','-'),'/','.'),' ','.'),2),'-','.'),
REPLACE(PARSENAME(REPLACE(REPLACE(REPLACE('30.5 mg/30.5 ml','.','-'),'/','.'),' ','.'),1),'-','.')
[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-13 : 03:22:30

[code]
try this once
DECLARE @PARAMETERVALUE varchar(32),@first int, @third int
SELECT @PARAMETERVALUE = '30.000 mg/30.056 ml'

SELECT @first = CHARINDEX('.',@PARAMETERVALUE),@third =CHARINDEX('.',SUBSTRING(@PARAMETERVALUE,CHARINDEX('/',@PARAMETERVALUE)+1,len(@PARAMETERVALUE)))

SELECT @PARAMETERVALUE = REPLACE(REPLACE(REPLACE(@PARAMETERVALUE,'.',''),'/','.'),' ','.')

SELECT
LEFT(parsename(@PARAMETERVALUE,4),@first-1)+'.'+right(parsename(@PARAMETERVALUE,4),len(parsename(@PARAMETERVALUE,4))-(@first-1)),
parsename(@PARAMETERVALUE,3),
LEFT(parsename(@PARAMETERVALUE,2),@third-1)+'.'+right(parsename(@PARAMETERVALUE,2),len(parsename(@PARAMETERVALUE,2))-(@third-1)),
parsename(@PARAMETERVALUE,1)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources