exracting data from a column - 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

General SQL Server Forums

New to SQL Server Programming

exracting data from a column

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2012-05-11 : 11:00:24

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-11 : 11:19:41

see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79083&SearchTerms=fnFilterString

KH
[spoiler]Time is always against us[/spoiler]

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-14 : 05:35:33

This might help.


--Creating Table

Create Table Ex
(col1 varchar(30),
 col2 varchar(30) )


--Inserting Sample Data

Insert Into Ex
Select 'pressue*0.897', ''
Union ALL
Select 'height*1.65', ''
Union ALL
Select 'breadth/12', ''


--Query for Your Requirement(Method 1)

Select Col1,
(Case When Col1 = 'pressue*0.897' then 'pressure'
	  When Col1 = 'height*1.65' then 'height'
	  When Col1 = 'breadth/12' then 'breadth'
	  Else ''
 End) As Col2
From Ex


--Query for Your Requirement(Method 2)

Select Col1,
(Case When Col1 = 'pressue*0.897' then SUBSTRING(Col1, 1, (CHARINDEX('*',Col1,1)-1) )
	  When Col1 = 'height*1.65' then SUBSTRING(Col1, 1, (CHARINDEX('*',Col1,1)-1) )
	  When Col1 = 'breadth/12' then SUBSTRING(Col1, 1, (CHARINDEX('/',Col1,1)-1) )
	  Else ''
 End) As Col2
From Ex

N 28° 33' 11.93148"
E 77° 14' 33.66384"

divyaram
Posting Yak Master

180 Posts

Posted - 2012-05-14 : 06:55:16

Hi Vinu,

Thx for your help... but data given above is jus a sample data only...the real scenario has a lots of data...
i got out with logic like this....
i am posting here because it may help others in future

select distinct DB,pColumn,ptable, TColumn
,case when PATINDEX('%*%',TColumn)<> 0 then
replace(SUBSTRING(TColumn,PATINDEX('(',TColumn), PATINDEX('%*%',TColumn)),'(','')
when PATINDEX('%/%',TColumn)<> 0 then
replace(SUBSTRING(TColumn,PATINDEX('(',TColumn), PATINDEX('%/%',TColumn)),'(','')
else ''
end as rep
from table

quote:

Originally posted by vinu.vijayan

This might help.


--Creating Table

Create Table Ex
(col1 varchar(30),
 col2 varchar(30) )


--Inserting Sample Data

Insert Into Ex
Select 'pressue*0.897', ''
Union ALL
Select 'height*1.65', ''
Union ALL
Select 'breadth/12', ''


--Query for Your Requirement(Method 1)

Select Col1,
(Case When Col1 = 'pressue*0.897' then 'pressure'
	  When Col1 = 'height*1.65' then 'height'
	  When Col1 = 'breadth/12' then 'breadth'
	  Else ''
 End) As Col2
From Ex


--Query for Your Requirement(Method 2)

Select Col1,
(Case When Col1 = 'pressue*0.897' then SUBSTRING(Col1, 1, (CHARINDEX('*',Col1,1)-1) )
	  When Col1 = 'height*1.65' then SUBSTRING(Col1, 1, (CHARINDEX('*',Col1,1)-1) )
	  When Col1 = 'breadth/12' then SUBSTRING(Col1, 1, (CHARINDEX('/',Col1,1)-1) )
	  Else ''
 End) As Col2
From Ex

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Regards,
Divya

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-14 : 07:21:38

quote:
Originally posted by divyaram

Hi Vinu,

Thx for your help... but data given above is jus a sample data only...the real scenario has a lots of data...
i got out with logic like this....
i am posting here because it may help others in future

select distinct DB,pColumn,ptable, TColumn
,case when PATINDEX('%*%',TColumn)<> 0 then
replace(SUBSTRING(TColumn,PATINDEX('(',TColumn), PATINDEX('%*%',TColumn)),'(','')
when PATINDEX('%/%',TColumn)<> 0 then
replace(SUBSTRING(TColumn,PATINDEX('(',TColumn), PATINDEX('%/%',TColumn)),'(','')
else ''
end as rep
from table

quote:
Originally posted by vinu.vijayan

This might help.

--Creating Table

Create Table Ex
(col1 varchar(30),
col2 varchar(30) )

--Inserting Sample Data

Insert Into Ex
Select 'pressue*0.897', ''
Union ALL
Select 'height*1.65', ''
Union ALL
Select 'breadth/12', ''

--Query for Your Requirement(Method 1)

Select Col1,
(Case When Col1 = 'pressue*0.897' then 'pressure'
When Col1 = 'height*1.65' then 'height'
When Col1 = 'breadth/12' then 'breadth'
Else ''
End) As Col2
From Ex

--Query for Your Requirement(Method 2)

Select Col1,
(Case When Col1 = 'pressue*0.897' then SUBSTRING(Col1, 1, (CHARINDEX('*',Col1,1)-1) )
When Col1 = 'height*1.65' then SUBSTRING(Col1, 1, (CHARINDEX('*',Col1,1)-1) )
When Col1 = 'breadth/12' then SUBSTRING(Col1, 1, (CHARINDEX('/',Col1,1)-1) )
Else ''
End) As Col2
From Ex

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Regards,
Divya

Hi Divya,

My query was just to give you a head start and I was waiting for the reply where you tell me that there is more data.

That is when I was about to give you this query. But, you have worked it out already. Good work!!

N 28° 33' 11.93148"
E 77° 14' 33.66384"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-14 : 09:33:19

select col1,substring(col1,1,patindex('%[^a-z]%',col1)-1) as col2 from table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources