Need help in retrieving value - 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)

Need help in retrieving value

Author

Topic

SDondeti
Starting Member

7 Posts

Posted - 2009-09-24 : 10:36:33

Hello,

Bellow are field "ID" values, these are only one column 'ID' values.
4F62 D4 2h 5x
4F62 D4 4h 5x
4F62 D4 8h 5x
4F62 D4 24h
4F63 D4 2h 5x

There are 400 records for each report the i need to generate every week.

Bassed on the values given above i need to group by the value '62' after the charecter F, and then retrieve other fields and generate the report. F & D will be there in all the records.

Thanks for your help.

Sudhakar Dondeti

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-24 : 10:41:33

Can you provide ur expected output for this set of sample data.

SDondeti
Starting Member

7 Posts

Posted - 2009-09-24 : 10:49:20

quote:
Originally posted by vijayisonly

Can you provide ur expected output for this set of sample data.

I need to substring the value between F & D and pass it to a substring and retrieve the remaining fields.
so above example data:
i get
62
62
62
62
63

pass this value to the subquery and retrieve other fields.

Thank you..

Sudhakar Dondeti

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-24 : 10:56:42

select substring(id,3,3) from your_table

Madhivanan

Failing to plan is Planning to fail

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-24 : 10:57:15

This will give you all your "62"s...you can include this in your subquery..

select substring(col1,charindex('F',[ID]) + 1, charindex('D',[ID]) - charindex('F',[ID]) -1 )
from <urtable>

SDondeti
Starting Member

7 Posts

Posted - 2009-09-24 : 11:41:24

Thank you Vijay, I tried your logic as bellow

select substring([Sample ID],charindex('F',[Sample ID]) + 1, charindex('D',[Sample ID]) - charindex('F',[Sample ID]) -1 )
from XLImport

got error message as : Invalid length parameter passed to the SUBSTRING function.

i tried retrieving index for F & D and got successfully and when i add this in substring i am getting invalid length parameter to the substring.

Any correction required in the code? Thank you..

Sudhakar Dondeti

SDondeti
Starting Member

7 Posts

Posted - 2009-09-24 : 12:04:18

Vijay, Its Great, got the solution. Thanks again.

Sudhakar Dondeti

SDondeti
Starting Member

7 Posts

Posted - 2009-09-24 : 12:05:28

Thanks you madhivanan.

Sudhakar Dondeti

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-24 : 12:05:29

welcome

quote:
Originally posted by SDondeti

Vijay, Its Great, got the solution. Thanks again.

Sudhakar Dondeti

SDondeti
Starting Member

7 Posts

Posted - 2009-09-24 : 14:57:37

Hi Vijay,
Slight modification in the requirement,
As i asked you to select value between F & D, some times F may varies with M like Male or F like Female.
Example:
4F62 D4 2h 5x
4M62 D4 4h 5x
4F63 D4 8h 5x
4M63 D4 24h
4F64 D4 2h 5x

my expected output is:
62
62
63
63
64

Thanks you...

Sudhakar Dondeti

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-24 : 15:51:08

Try like this...

SELECT CASE 
         WHEN charindex('F',[ID]) > 0 
         THEN substring(col1,charindex('F',[ID]) + 1,charindex('D',[ID]) - charindex('F',[ID]) - 1) 
         ELSE substring(col1,charindex('M',[ID]) + 1,charindex('D',[ID]) - charindex('M',[ID]) - 1) 
       END 
FROM   urtable

quote:
Originally posted by SDondeti

Hi Vijay,
Slight modification in the requirement,
As i asked you to select value between F & D, some times F may varies with M like Male or F like Female.
Example:
4F62 D4 2h 5x
4M62 D4 4h 5x
4F63 D4 8h 5x
4M63 D4 24h
4F64 D4 2h 5x

my expected output is:
62
62
63
63
64

Thanks you...

Sudhakar Dondeti

SDondeti
Starting Member

7 Posts

Posted - 2009-09-24 : 16:03:48

Great, Thank you Vijay.

Sudhakar Dondeti

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-25 : 02:51:43

quote:
Originally posted by SDondeti

Hi Vijay,
Slight modification in the requirement,
As i asked you to select value between F & D, some times F may varies with M like Male or F like Female.
Example:
4F62 D4 2h 5x
4M62 D4 4h 5x
4F63 D4 8h 5x
4M63 D4 24h
4F64 D4 2h 5x

my expected output is:
62
62
63
63
64

Thanks you...

Sudhakar Dondeti

You should have tried my solutions too

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources