August 7, 2014 at 4:30 am
hi friends i have small doubt in sql server how to solve this issue
Table:emp
sn | code | sal
1 | 1100F- | 100
2 | | 200
3 | 97535-GO | 300
4 |97530-GO | 300
5 | | 600
Based on this table data i want output like below
sn | code | Changed | sal
1 | 1100F | NA | 100
2 | 0 | NA | 200
3 | 97535 | GO | 300
4 | 97530 | GO |300
5 | 0 | NA |600
here we have one more column requirec that is chnaged columns that information we consider after - symbol data.
i tried like
select pn,code,salcase WHEN CHARINDEX('-',code)>0 THEN SUBSTRING(code,CHARINDEX('-',code)+1,len(code)) ELSE 'NA' END changed
from emp
after ran query
i missed sn =1 record does not have come 'NA' Values in changed columns.please let me know where i done mistick.
August 7, 2014 at 4:40 am
If you set up your sample data as CREATE TABLE ... followed by INSERT to populate it, I'm sure your problem will be solved in minutes.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 7, 2014 at 5:06 am
A little dirty but works
create table #emp
(
sn int,
code varchar(10),
sal int
)
insert into #emp values
(1,'1100F-',100),
(2,'',200),
(3,'97535-GO',300),
(4,'97530-GO',300),
(5,'',600)
select * from #emp
select
sn,
isnull(
nullif(
replace(
left(code,
charindex('-',code)
),
'-','')
,'')
,'0') as code,
isnull(
nullif(
right(code,
len(code)
-
charindex('-',code)
)
,'')
,'NA') as changed,
Sal
FROM
#emp
drop table #emp
August 7, 2014 at 8:12 am
Probably no better than Anthony's, but using the 8K Delimited Splitter[/url] : -
IF EXISTS ( SELECT 1
FROM sys.[objects]
WHERE [name] = 'DelimitedSplit8K'
AND type = 'IF' )
BEGIN;
DROP FUNCTION [dbo].[DelimitedSplit8K];
END;
GO
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
GO
SELECT e.sn,
ISNULL(NULLIF(MAX(CASE WHEN ItemNumber = 1 THEN Item ELSE '' END),''),'0') AS code,
ISNULL(NULLIF(MAX(CASE WHEN ItemNumber = 2 THEN Item ELSE '' END),''),'NA') AS changed,
e.sal
FROM [#emp] AS e
CROSS APPLY [dbo].[DelimitedSplit8K](e.code, '-') AS de
GROUP BY e.sn, e.sal;
Which would also return: -
sn code changed sal
----------- ------ ------- -----------
1 1100F NA 100
2 0 NA 200
3 97535 GO 300
4 97530 GO 300
5 0 NA 600
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply