當前位置:首頁 » 存儲配置 » 存儲過程分割字元串

存儲過程分割字元串

發布時間: 2024-12-24 13:40:47

A. 如何拆分調用sql存儲過程後返回的字元串

string ID = Table[0].Rows[0][0].ToString();

sting[] arrID=ID.split(',');

把對象轉換成字元串不就可以了嗎?呵呵
希望對你有幫助

B. 求助:如何在存儲過程中拆分字元串

declare @inputStr varchar(max)set @inputStr='H,SH600,AAA,123,456,567,566,565,H,SH600,ABC,542,34,35,367,765,H,SH600,AAC,52,314,325,3867,7865'declare @divideFlag varchar(20)set @divideFlag='H,'declare @bnname varchar(8)declare @gpdm varchar(8)declare @gpcn intdeclare @ydate intdeclare @yopen intdeclare @yhigh intdeclare @substr varchar(200)declare @pos1 intdeclare @pos2 intdeclare @sql varchar(400)--如果字元串中有分隔符標記while CHARINDEX(@divideFlag,@inputStr,0)0begin set @pos1 = CHARINDEX(@divideFlag,@inputStr,0) print '位置1 '+cast(@pos1 as varchar) set @pos2 = CHARINDEX(@divideFlag,@inputStr,@pos1+1) print '位置2 ' + cast(@pos2 as varchar) if @pos20 begin set @substr = SUBSTRING(@inputStr,@pos1,@pos2-@pos1) print '截取處理串 '+ @substr set @inputStr = SUBSTRING(@inputStr,@pos2,len(@inputStr)) --print @inputStr end else begin set @substr = SUBSTRING(@inputStr,@pos1,len(@inputStr)) print '截取處理串 '+ @substr set @inputStr = 'empty' --print @inputStr end --去掉開頭的分割標記 set @substr=substring(@substr,CHARINDEX(@divideFlag,@substr,0)+len(@divideFlag),len(@substr)) print '去掉開頭分割標記 ' + @substr if right(@substr,1)=',' begin set @substr = left(@substr,len(@substr)-1) print '去掉末尾的逗號' + @substr end --取得表名稱 set @bnname = substring(@substr,1,charindex(',',@substr,0)-1) print @bnname --取表名稱後面的字元串 set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr --取得第一個欄位 set @gpdm = substring(@substr,1,charindex(',',@substr,0)-1) print @gpdm --取得後面的字元串 set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr set @gpcn = substring(@substr,1,charindex(',',@substr,0)-1) print @gpcn set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr set @ydate = substring(@substr,1,charindex(',',@substr,0)-1) print @ydate set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr set @yopen = substring(@substr,1,charindex(',',@substr,0)-1) print @yopen set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr set @yhigh = substring(@substr,1,charindex(',',@substr,0)-1) print @yhigh set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr -- 拼接 插入的 sql 字元串 set @sql = 'insert into '+@bnname+' (gpdm,gpcn,ydate,yopen,yhigh) values( '''+@gpdm+''','+cast(@gpcn as varchar)+','+cast(@ydate as varchar)+','+cast(@yopen as varchar)+','+cast(@yhigh as varchar)+' )' print @sqlend-- 拷貝到查詢分析器執行,可以看到列印出的消息,拼接的字元串應該可以用

C. oracle存儲過程怎麼截取字元串

之前我保存的經典的方法,你可以參考下
首先創建一張表t_al_test_sy
欄位分別為:pid,str_id,str_dm,str_mc;
存儲過程如下:
create or replace procere p_al_output
as
str_id varchar(60);
str_dm varchar(60);
str_mc varchar(800);
str_start number;
str_len number;
str_length number;
str_end number;
str_count number;
str_content varchar2(800);
str_partcontent varchar2(800);
str_iscontent number;

D. oracle存儲過程傳入一個字元串參數'1,2,3,4,5,6,7,8',如何分割並轉為數字

create or replace type type_split as table of varchar2(50); --創建一個type,如果為了使split函數具有通用性,請將其size 設大些。

--創建function
create or replace function split
(
p_list varchar2,
p_sep varchar2 := ','
) return type_split pipelined
is
l_idx pls_integer;
v_list varchar2(50) := p_list;
begin
loop
l_idx := instr(v_list,p_sep);
if l_idx > 0 then
pipe row(substr(v_list,1,l_idx-1));
v_list := substr(v_list,l_idx+length(p_sep));
else
pipe row(v_list);
exit;
end if;
end loop;
return;
end split;

使用:
select * from table(split('1,2,3,4,5,6,7,8'
,','));
然後就可以通過「,」來分割數字了

E. 關於SQLserver 存儲過程 如何切割一個字元串 轉化為數組

Create Function [dbo].[Split](@Sql varchar(8000),@Splits varchar(10))
returns @temp Table (a varchar(100))
As
Begin
Declare @i Int
Set @Sql = RTrim(LTrim(@Sql))
Set @i = CharIndex(@Splits,@Sql)
While @i >= 1
Begin
Insert @temp Values(Left(@Sql,@i-1))
Set @Sql = SubString(@Sql,@i+1,Len(@Sql)-@i)
Set @i = CharIndex(@Splits,@Sql)
End
If @Sql <> ''
Insert @temp Values (@Sql)
Return
End

F. 關於oracle 存儲過程 如何切割一個字元串 轉化為字元數組,然後遍歷該數組: 類似ab,12;cd,55;k,7

給你寫了個包,包體代碼如下:
CREATE OR REPLACE PACKAGE BODY Pkg_Bai IS
-- 自定義個數組類型
TYPE Typ_Str_Array IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
PROCEDURE Prc_String_To_Array(Pv_i_String IN VARCHAR2,
Pv_i_Identifier IN VARCHAR2,
Pt_o_Array OUT Typ_Str_Array) IS

Lv_Tmpstr VARCHAR2(200);
Lv_Changed_String VARCHAR2(4000);

BEGIN

-- 初始化
Lv_Changed_String := Pv_i_String;

-- 拆分邏輯
-- 1.字元串中沒有標識符
IF Instr(Pv_i_String, Pv_i_Identifier, 1, 1) = 0 THEN
Pt_o_Array(Pt_o_Array.Count + 1) := Pv_i_String;
ELSE

-- 2.字元串中有標識符
WHILE Instr(Lv_Changed_String, Pv_i_Identifier, 1, 1) > 0 LOOP
Lv_Tmpstr := Substr(Lv_Changed_String,
1,
Instr(Lv_Changed_String, Pv_i_Identifier, 1, 1) - 1);

Pt_o_Array(Pt_o_Array.Count + 1) := Lv_Tmpstr;
Lv_Changed_String := Substr(Lv_Changed_String,
Instr(Lv_Changed_String, Pv_i_Identifier, 1, 1) + 1,
Length(Lv_Changed_String));
END LOOP;

-- 3.最有一個被拆分出來的字元串中不含標識符
IF NOT Lv_Changed_String IS NULL THEN
-- 將最後一個保存
Pt_o_Array(Pt_o_Array.Count + 1) := Lv_Changed_String;
END IF;

END IF;

EXCEPTION
WHEN OTHERS THEN
NULL;
END;

PROCEDURE Prc_Test(Pv_i_Str IN VARCHAR2) IS
--定義數組
Lt_Array Typ_Str_Array;
Ln_Count NUMBER; -- 數組元素個數
Ln_Num NUMBER;
Lv_Value VARCHAR2(30);
BEGIN

Prc_String_To_Array(Pv_i_Str, ';', Lt_Array);
-- 循環數組
FOR i IN 1 .. Lt_Array.Count LOOP
-- 獲取每個元素中數值
Ln_Num := To_Number(TRIM(Substr(Lt_Array(i), Instr(Lt_Array(i), ',', -1) + 1)));
IF Ln_Num > 20 THEN
Lv_Value := TRIM(Substr(Lt_Array(i), 1, Instr(Lt_Array(i), ',', -1) - 1));
INSERT INTO a (col_a) VALUES (lv_value);
END IF;
END LOOP;
END;

END Pkg_Bai;

調用Prc_Test來分隔字元串並按要求比較後插入a表。我測試過,沒有問題。
需要注意的是,數據的要求:字元串中每組數據間使用英文小寫分號分隔,同一組數據字母在前,數字在後,以英文小寫逗號分隔。測試一下吧。
歡迎採納,不採納你都對不起我這20分鍾。呵呵......

熱點內容
圖靈破譯二戰德國密碼叫什麼 發布:2024-12-25 03:53:12 瀏覽:767
java集合類 發布:2024-12-25 03:43:13 瀏覽:556
在qq怎麼找到郵箱密碼 發布:2024-12-25 03:34:51 瀏覽:876
速訊app安卓版在哪裡下載 發布:2024-12-25 03:32:30 瀏覽:587
交換機伺服器路由器電腦怎麼連接 發布:2024-12-25 03:10:42 瀏覽:506
javaweb實現文件上傳 發布:2024-12-25 03:10:41 瀏覽:350
秀哥php 發布:2024-12-25 02:53:52 瀏覽:776
centos安裝phpapache 發布:2024-12-25 02:49:41 瀏覽:759
隨機優化演算法 發布:2024-12-25 02:38:09 瀏覽:279
phpnullempty 發布:2024-12-25 02:30:39 瀏覽:592