은서야 서진아 아빠랑 사진 찍자~

꽤나 오래전부터 검색해봐도 적절한 결과를 얻지 못했었는데

오늘에야 발견... 뒷북일 가능성도 농후합니다만, 포스팅해봅니다





먼저 원문 주소 나갑니다



http://weblogs.sqlteam.com/brettk/archive/2005/02/02/4095.aspx



가장 생각하기 쉽고, 또 제가 얼마전까지 사용해왔던 while 문에 대해

Terrific looping technique! 이라고 지껄여 주는 저 센스...





다음은 제가 필요에 의해 간소화한 응용편 소스 나갑니다



--pivot table은 미리 생성하고 필요한만큼 로우 생성

create table pivot(

	seq int not null primary key

)



declare @seq int

select @seq = 1

while @seq <= 100

begin

	insert into pivot values(@seq)

	select @seq = @seq + 1

end

go



declare @str varchar(1000), @delim varchar(10)

select @str = '1,2,3,4,5,6,7,8,9,0', @delim = ','



select substring(@str, pos1+1, case pos2 when 0 then len(@str) - pos1 else pos2 - pos1 - 1 end) as token

from

(

	select pos1,charindex(@delim,@str,pos1+1) pos2 from 

	( 

		select charindex(@delim,@str,seq) pos1

		from pivot

		where seq <= len(@str)

	) d1 group by pos1

) d2

order by pos1

go



뭐, 굳이 실행안해보셔도 결과는 예상하시다시피 다음과 같습니다



token

------------------------------------------------ 

1

2

3

4

5

6

7

8

9

0



(10 row(s) affected)

db에서 사원명을 조회하는데, 외자이름을 가진 사람과 3자리 이름을 가진 사람이 혼재할 경우,

조회결과를 복사하면 정렬이 맞지 않아 text 로 활용할때 불편할때가 있다.

오라클에서는 lpad, rpad 내장함수를 이용해 쉽게 왼쪽, 오른쪽을 공백등으로 채울수가 있는데,

mssql에서는 해당 함수가 없어

REPLICATE() 함수를 이용한 function을 만들었다.

sqler 사이트등의 답변에도 소개가 되어있으나 간과한 내용이 있어 보완하자면,

한글은 2byte 이므로 함수내에서 공백을 채울 자리수 계산할때 len() 이 아니라

datalenth() 를 이용해야 한다.

그리고, 채울문자를 char 형이 아니라 varchar 로 하여 한글과 같은 2byte 문자로도 채우기가 가능하도록 하였다.

마지막으로, 대체할 문자열이 null 이거나 공백일 경우는 '-' 문자로 임의 대체후 채워지도록 하였다.

CREATE FUNCTION dbo.FN_LPAD
(@input VARCHAR(100), @ct INT, @chr varchar(10))
RETURNS varchar(100)
AS
BEGIN
  --@ct자리수에서 @input이 모자라는 만큼 @chr로 앞에 채우기
  --샘플 : @Mon를 2자리 수로 만들고 앞에 0 붙이기
  --select dbo.FN_LPAD(@Mon, 2, '0')
  if @input = '' or @input is null begin set @input = '-'; end;
  RETURN (REPLICATE(@chr, @ct-datalength(@input))+@input)
END

CREATE FUNCTION dbo.FN_RPAD
(@input VARCHAR(100), @ct INT, @chr varchar(10))
RETURNS varchar(100)
AS
BEGIN
  --@ct자리수에서 @input이 모자라는 만큼 뒤에서 @chr로 채우기
  --샘플 : @Mon를 2자리 수로 만들고 뒤에 0 붙이기
  --select dbo.FN_LPAD(@Mon, 2, '0')
  if @input = '' or @input is null begin set @input = '-'; end;
  RETURN (@input + REPLICATE(@chr, @ct-datalength(@input)))
END

select dbo.FN_RPAD(b.deptnm,25,' ')  부서
      ,dbo.FN_RPAD(b.name,10,' ')    이름
from   damdang b
where  b.name = '홍길동'
order by 1,2

---함수생성하지 않을 경우

select  b.deptnm+REPLICATE(' ', 25-datalength(b.deptnm)) 부서
      ,b.name+REPLICATE(' ', 25-datalength(b.name)) 부서
from   damdang b
where  ( len(b.name) < 3 or b.name in ('홍길동'))
order by 1,2