Query problem - 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)

Query problem

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-04-20 : 08:16:44

I have a different codes and its description available in table.
e.g.
---- table test1 and column is codes
KOE2M LSE1F ZZE3R

in above code.. KOE2M.. "KOE2" is code available in database with its description and "M" is identifier..
there are four identifiers only.. M is for Missing, F for failure and R for Removed..

so i want result like..

--------- output
Kiosk desc Missing LSE1 desc Failure ZZZE3 Removed

---
in above output i will fetch description from code and last character for its identifier desc and so one..

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-04-20 : 08:59:45

[code]
SELECT SUBSTRING(String, 0, 5) + CASE SUBSTRING(String, 5, 1) WHEN 'M' THEN ' Missing' WHEN 'F' THEN ' Failure' WHEN 'R' THEN ' Removed' END,
SUBSTRING(String, 7, 4) + CASE SUBSTRING(String, 11, 1) WHEN 'M' THEN ' Missing' WHEN 'F' THEN ' Failure' WHEN 'R' THEN ' Removed' END,
SUBSTRING(String, 13, 4) + CASE SUBSTRING(String, 17, 1) WHEN 'M' THEN ' Missing' WHEN 'F' THEN ' Failure' WHEN 'R' THEN ' Removed' END
FROM (
SELECT 'KOE2M LSE1F ZZE3R' as [String])a
[/code]

Hope can help...but advise to wait pros with confirmation...

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-04-20 : 09:04:20

That is fine waterduck but i need it in one column and also From Code i need descrip which comes from DB..

i have done something like... but still in confusion.. is there any other way?

THIS GIVES TABLE OUTPUT but I REQUIRED it in one column only with concatenation

declare @strcode varchar(500)
set @strcode = 'A20M A21R KKSER3M '
--select charindex(' ', @strcode, 6)
--select substring(@strcode, charindex(' ', @strcode, 6) - 1, 1)
--select substring(@strcode, 0, charindex(' ',@strcode) - 1)
declare @coel varchar(3000)
;WITH CTE (startpos, posit) as
(
select 1 as startpos, charindex(' ',@strcode,1) as posit
union all
select posit + 1 as startpos, charindex(' ', @strcode, posit + 1) as posit
from cte where charindex(' ', @strcode, posit + 1) <> 0
)
select substring(@strcode, startpos, posit-startpos-1) + ' ' + substring(@strcode, posit-1, 1), * from cte

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-04-20 : 09:31:56

[code]
SELECT SUBSTRING(String1, 0, LEN(String1))+CASE SUBSTRING(REVERSE(String1), 1, 1) WHEN 'M' THEN ' Missing ' WHEN 'F' THEN ' Failure ' WHEN 'R' THEN ' Removed ' END +
SUBSTRING(String2, 0, LEN(String2))+CASE SUBSTRING(REVERSE(String2), 1, 1) WHEN 'M' THEN ' Missing ' WHEN 'F' THEN ' Failure ' WHEN 'R' THEN ' Removed ' END +
SUBSTRING(String3, 0, LEN(String3))+CASE SUBSTRING(REVERSE(String3), 1, 1) WHEN 'M' THEN ' Missing ' WHEN 'F' THEN ' Failure ' WHEN 'R' THEN ' Removed ' END
FROM (
SELECT SUBSTRING(String, 0, CHARINDEX(' ', String))String1,
SUBSTRING(String, CHARINDEX(' ', String) + 1, CHARINDEX(' ', String) - 1)String2,
REVERSE(SUBSTRING(REVERSE(String), 0, CHARINDEX(' ', REVERSE(String))))String3
FROM (
SELECT 'KOE2M LSE1F ZZE3R' as [String]
)a
)b
[/code]

Hope can help...but advise to wait pros with confirmation...

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-04-20 : 09:38:34

hi waterduck..
thanks for your reply..

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-04-20 : 09:40:38

not quite sure correct or not...but if wrong please ask sql pros >"<

Hope can help...but advise to wait pros with confirmation...

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-04-20 : 09:42:39

:)
yes waterduck
you are correct per your side
but you can also run my CTE query too..
i can easily operate database operation to get description from code too..

thanks again..

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-04-20 : 09:43:58

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033#315323
Please refer to them

Hope can help...but advise to wait pros with confirmation...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-04-20 : 09:52:22

Visakh will come around shortly to show how this can be done with a cross apply, but I think this works for what you want

DECLARE @Code varchar(50)

SET @Code = 'KOE2M LSE1F ZZE3R'

SET @Code = REPLACE(@Code,' ','.')

SELECT PARSENAME(REPLACE(@Code,' ','.'),3) +
CASE RIGHT(PARSENAME(REPLACE(@Code,' ','.'),3),1)
WHEN 'M' THEN ' MISSING'
WHEN 'R' THEN ' REMOVED'
WHEN 'F' THEN 'FAILURE'
END as Code
,PARSENAME(REPLACE(@Code,' ','.'),2) + CASE RIGHT(PARSENAME(REPLACE(@Code,' ','.'),2),1)
WHEN 'M' THEN ' MISSING'
WHEN 'R' THEN ' REMOVED'
WHEN 'F' THEN ' FAILURE'
END
,PARSENAME(REPLACE(@Code,' ','.'),1) + CASE RIGHT(PARSENAME(REPLACE(@Code,' ','.'),1),1)
WHEN 'M' THEN ' MISSING'
WHEN 'R' THEN ' REMOVED'
WHEN 'F' THEN 'FAILURE'
END

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-20 : 10:01:47

how will the values be stored in db? are codes and identifiers both stored in same table with description?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xpandre
Posting Yak Master

212 Posts

Posted - 2010-04-20 : 10:08:59

[code]declare @strcode varchar(500)
set @strcode = 'A20M A21R KKSER3M abcR '
declare @coel varchar(3000)
;WITH CTE (startpos, posit) as
(
select 1 as startpos, charindex(' ',@strcode,1) as posit
union all
select posit + 1 as startpos, charindex(' ', @strcode, posit + 1) as posit
from cte where charindex(' ', @strcode, posit + 1) <> 0
), cte2 as(
select substring(substring(@strcode,startpos,posit-startpos),1,len(substring(@strcode,startpos,posit-startpos))-1) a1,
case
when substring(substring(@strcode,startpos,posit-startpos),len(substring(@strcode,startpos,posit-startpos)),1) = 'M'
then ' Missing'
when substring(substring(@strcode,startpos,posit-startpos),len(substring(@strcode,startpos,posit-startpos)),1) = 'R'
then ' Removed'
end a2

from cte ),
cte3 as
(
select distinct bb from cte2 tt2 cross apply (select a1 + a2+' ' from cte2 for xml path('')) as c(bb))
select * from cte3[/code]

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-04-21 : 01:13:43

quote:
Originally posted by visakh16

how will the values be stored in db? are codes and identifiers both stored in same table with description?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes visakh my whole code string will be stored in one column
as: "KOE2M LSE1F ZZE3R" and code is in one table with its description
AND code can be of any length.. suppose KOE2 is first code in previous string and M is identifier.. then other code can be short or long..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 01:17:04

will format be always consistent? 4 characters of code followed by 1 char identifier in each word?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-04-21 : 01:19:12

yes.. code can be of any lengh but 1 char identifier is fix..
like
KOOS2M
KOOM
KOO332M
KOO33M

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-04-21 : 02:12:00

quote:

Originally posted by xpandre

declare @strcode varchar(500)
set @strcode = 'A20M A21R KKSER3M abcR '
declare @coel varchar(3000)
;WITH CTE (startpos, posit) as
(
select 1 as startpos, charindex(' ',@strcode,1) as posit
union all
select posit + 1 as startpos, charindex(' ', @strcode, posit + 1) as posit
from cte where charindex(' ', @strcode, posit + 1) <> 0
), cte2 as(
select substring(substring(@strcode,startpos,posit-startpos),1,len(substring(@strcode,startpos,posit-startpos))-1) a1,
case 
when substring(substring(@strcode,startpos,posit-startpos),len(substring(@strcode,startpos,posit-startpos)),1) = 'M' 
then ' Missing'
when substring(substring(@strcode,startpos,posit-startpos),len(substring(@strcode,startpos,posit-startpos)),1) = 'R' 
then ' Removed'
end a2

from cte ),
cte3 as
(
select distinct bb from cte2 tt2 cross apply (select a1 + a2+' ' from cte2 for xml path('')) as c(bb))
select * from cte3

Thanks..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 06:03:28

quote:
Originally posted by keyursoni85

yes.. code can be of any lengh but 1 char identifier is fix..
like
KOOS2M
KOOM
KOO332M
KOO33M

you can use a function to parse the string and then use join to lookup values based on individual values. something like


SELECT  n.Description + ' ' + o.Description
FROM
(
SELECT LEFT(f.Val,LEN(f.Val)-1) AS code,RIGHT(f.Val,1) AS Id
FROM YourTable t
CROSS APPLY dbo.ParseValues(t.codes,' ')f
)m
CROSS APPLY (SELECT TOP 1 Description
             FROM YourlookupTable l
             WHERE code LIKE m.code + '%'
             ORDER BY LEN(code) DESC) n
CROSS APPLY (SELECT Description
             FROM YourlookupTable l
             WHERE code =m.ID ) o

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-04-21 : 06:15:06

quote:
Originally posted by visakh16

quote:
Originally posted by keyursoni85

yes.. code can be of any lengh but 1 char identifier is fix..
like
KOOS2M
KOOM
KOO332M
KOO33M

you can use a function to parse the string and then use join to lookup values based on individual values. something like

SELECT n.Description + ' ' + o.Description
FROM
(
SELECT LEFT(f.Val,LEN(f.Val)-1) AS code,RIGHT(f.Val,1) AS Id
FROM YourTable t
CROSS APPLY dbo.ParseValues(t.codes,' ')f
)m
CROSS APPLY (SELECT TOP 1 Description
FROM YourlookupTable l
WHERE code LIKE m.code + '%'
ORDER BY LEN(code) DESC) n
CROSS APPLY (SELECT Description
FROM YourlookupTable l
WHERE code =m.ID ) o

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks visakh.. the last cross apply which you have used above.. i doesnt need it as my last character is static identifier like Missing.. Failure etc..
But above thing give me a tabled rows for each description code..
didnt getttng above query.. currently i have used below one..

declare @strcode varchar(500)
set @strcode = 'A20M A21R KKSER3M abcR '
declare @coel varchar(3000)
;WITH CTE (startpos, posit) as
(
select 1 as startpos, charindex(' ',@strcode,1) as posit
union all
select posit + 1 as startpos, charindex(' ', @strcode, posit + 1) as posit
from cte where charindex(' ', @strcode, posit + 1) <> 0
), cte2 as(
select substring(substring(@strcode,startpos,posit-startpos),1,len(substring(@strcode,startpos,posit-startpos))-1) a1,
case
when substring(substring(@strcode,startpos,posit-startpos),len(substring(@strcode,startpos,posit-startpos)),1) = 'M'
then ' Missing'
when substring(substring(@strcode,startpos,posit-startpos),len(substring(@strcode,startpos,posit-startpos)),1) = 'R'
then ' Removed'
end a2

from cte ),
cte3 as
(
select distinct bb from cte2 tt2 cross apply (select a1 + a2+' ' from cte2 for xml path('')) as c(bb))
select * from cte3

Because i need it in one row for my series of codes..

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-04-21 : 08:17:45

DECLARE @Code varchar(50)
SET @Code = 'KOE2M LSE1F ZZE3R'
SET @Code = REPLACE(@Code,' ','.')

SELECT PARSENAME(REPLACE(@Code,' ','.'),3) + t1.CodeDesc
+' '+ PARSENAME(REPLACE(@Code,' ','.'),2) + t1.CodeDesc
+' '+ PARSENAME(REPLACE(@Code,' ','.'),1) + t1.CodeDesc
FROM

(
SELECT [Code] = 'M',[CodeDesc] = ' Missing' UNION
SELECT 'R',' Removed' UNION
SELECT 'F',' Failure'
)
t1

WHERE RIGHT( PARSENAME(REPLACE(@Code,' ','.'),3),1) = t1.[code]

Everyday I learn something that somebody else already knew

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-04-22 : 05:18:49

quote:
Originally posted by jimf

DECLARE @Code varchar(50)
SET @Code = 'KOE2M LSE1F ZZE3R'
SET @Code = REPLACE(@Code,' ','.')

SELECT PARSENAME(REPLACE(@Code,' ','.'),3) + t1.CodeDesc
+' '+ PARSENAME(REPLACE(@Code,' ','.'),2) + t1.CodeDesc
+' '+ PARSENAME(REPLACE(@Code,' ','.'),1) + t1.CodeDesc
FROM

(
SELECT [Code] = 'M',[CodeDesc] = ' Missing' UNION
SELECT 'R',' Removed' UNION
SELECT 'F',' Failure'
)
t1

WHERE RIGHT( PARSENAME(REPLACE(@Code,' ','.'),3),1) = t1.[code]

Everyday I learn something that somebody else already knew

Above will give me.. below invalid output
---------------------------------
KOE2M Missing LSE1F ~~Missing~~ ZZE3R Missing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources