블로그 이미지
progh2
지루한 것에서 벗어나 재미난 것 속으로 풍덩~☆

calendar

 «  » 2024.5
일 월 화 수 목 금 토
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31

Notice

Recent Post

Recent Comment

Archive

2012. 3. 11. 14:55 기술문서창고/mysql

엑셀, MySQL에서 한자리 숫자 앞에 '0'을 붙이기

이런 저런 자료를 작업하다보면 한자리 숫자 앞에 '0'을 붙여야 하는 경우가 종종 있다.
예를 들면 학번을 만들 때인데, 학년, 반, 번호 데이터를 가져다가 연달아 붙이는 경우를 들 수 있다.

예를 들어 3학년 1반 1번인 경우..  3101 로 만들어야 할 때가 바로 그 때이다. 

물론 아무생각없이 손으로 하나하나 입력해도 되겠지만..
사람 수가 좀 된다면 참으로 지루하지 않을 수 없다.
따라서 조금 구글링 등을 통해서 찾아봤다.

엑셀
엑셀의 경우는 참 단순히 해결된다. 
text라는 함수를 쓰면 참 허무하게 해결된다. 예를 들어  학년, 반, 번호가 각각 a1, b1, c1 셀에 있다고 할 때 

=concatenate( a1, b1, text(c1, "00" ) ) 


처럼 하면 해결된다. 즉 text가 일종의 양식을 지정하는 것이다.

이처럼 유용한 text함수는 text(c1, "00번")  처럼도 사용할 수 있어 매우 유용하니 잘 알아두자. ^^

MySQL
MySQL의 경우는 쪼~금 복잡한데 
나의 경우는 관련 포맷팅 함수를 찾아보다 좀 단순무식하게 하는 방법을 찾아냇다. 
class_grade를 학년, class_num을 반, student_num을 번호라고 한다면, 다음과 같다. 

 cast( if( student_num < 10, concat(class_grade, class_num, '0',         student_num), concat(class_grade, class_num, student_num))  as char)


cast를 쓴 이유는 blob으로 결과가 리턴되어 결과를 볼 수 없어서 char형(문자열)로 바꿔주기 위함이다. 이런 부분에서는 엑셀의 text 함수가 매우 부러웠다.
저작자표시 비영리 동일조건
posted by progh2
2008. 10. 21. 22:09 기술문서창고/windows tip

MS-SQL에서 text 필드의 문자열을 우회해서 변경하는 SQL

textupdate인가 updatetext인가 하는 방법을 쓰는 법도 있지만,
여러모로 복잡한 고로 간단히 varchar 필드를 추가한 후, 여기에 text 필드의 값을
넣은 후 이 값을 replace로 치환, 다시 text 필드로 덮어쓰는 script를 만들어 보았습니다.

최근 악성코드때문에 고생하시는 분들은 있는데, 유용히 쓰였으면 좋겠네요.

아래 소스는 authors 라는 테이블에 text 속성인 desc1 칼럼이 있고 이 필드에 있는
<script url='http://'>악성코드</script>' 를 '<br>' 으로 변경할 경우를 가정한 것입니다.
적절히 수정해서 쓰세요~


-- 변수 선언
declare @target_column varchar(100)
declare @replace_source_string varchar(100)
declare @replace_target_string varchar(100)
declare @temp_column varchar(100)
declare @table_name varchar(100)
declare @sql varchar(1000)
declare @temp_column_size varchar(5)
set @temp_column='temp_column'
--//////////////////////////////////////////////
--
-- 경우에 맞게 적절하게 수정해서 사용
--
--//////////////////////////////////////////////
set @table_name='authors'
set @target_column='desc1'
set @replace_source_string='<script url=''''http://''''>악성코드</script>'
set @replace_target_string='<br>'
set @temp_column_size=4000
-- varchar형식의 임시 칼럼 추가
set @sql='alter table ' + @table_name + ' add ' + @temp_column + ' varchar('+ @temp_column_size +');'
exec (@sql)
-- 임시 칼럼으로 데이터 복사
set @sql='update ' + @table_name + ' set ' + @temp_column + '=' + @target_column
exec (@sql)
-- 원하는 문자열 제거
set @sql='update ' + @table_name + ' set ' + @temp_column + '=replace(' +@temp_column + ', ''' + @replace_source_string + ''', ''' + @replace_target_string +''')'
exec (@sql)
-- 임시 칼럼에서 원본 칼럼으로 데이터 덮어쓰기
set @sql='update ' + @table_name + ' set ' +  @target_column + '=' + @temp_column
exec (@sql)
-- 임시칼럼 삭제
set @sql='alter table ' + @table_name + ' drop column ' + @temp_column + ';'
exec (@sql)
-- 결과 조회
set @sql='select * from ' + @table_name;
exec (@sql)

textupdate.sql
posted by progh2
prev 1 next

티스토리툴바