Repository navigation
Expand file tree
/
Copy pathF_StringWithNumericAndAlpha.sql
More file actions
148 lines (114 loc) · 11.3 KB
/
Copy pathF_StringWithNumericAndAlpha.sql
File metadata and controls
148 lines (114 loc) · 11.3 KB
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
CREATE FUNCTION [dbo].[F_StringWithNumericAndAlpha](@input_string nvarchar(max))
RETURNS NVARCHAR(MAX)
AS
BEGIN
/*************************************************
The Goal Of This Function Is to take string which
combune letters in hebrew and numbers
and revers them.
This haapanes when we get string in flat files.
**************************************************/
DECLARE @str nvarchar(max) = @input_string;
DECLARE @ReturnStr nvarchar(max) = '';
DECLARE @StrLen int = len(ltrim(rtrim(@str)));
DECLARE @Index int = 1;
DECLARE @NumericStr nvarchar(max) = '';
DECLARE @PrevChar char(1);
DECLARE @CurrChar char(1);
DECLARE @NextChar char(1);
DECLARE @AlphaStr nvarchar(max) = '';
DECLARE @Special_Char_Ind int = case when patindex('%/%',@str)> 1 then 1 else 0 end;
DECLARE @Special_Char_Index int = patindex('%/%',@str);
DECLARE @StrLeftToSpecial nvarchar(max) = case when @Special_Char_Ind > 0 then substring(@str,1,@Special_Char_Index-1) else '' end;
DECLARE @StrRightToSpecial nvarchar(max) = case when @Special_Char_Ind > 0 then substring(@str,@Special_Char_Index+1,@StrLen) else '' end;
DECLARE @ReturnLeft nvarchar(max) = '';
DECLARE @ReturnRight nvarchar(max) = '';
IF @str IS NULL
RETURN NULL
IF @Special_Char_Ind = 0
begin
WHILE @Index <= @StrLen
BEGIN
SET @PrevChar = @CurrChar;
SET @CurrChar = substring(@Str,@Index,1)
SET @NextChar = substring(@Str,@Index+1,1)
SET @NumericStr = case when isnumeric(@CurrChar) = 0 then '' else @NumericStr end
SET @NumericStr += case when isnumeric(@CurrChar) = 1 then @CurrChar else '' end
SET @AlphaStr = case when isnumeric(@CurrChar) = 0 then @CurrChar else '' end
SET @ReturnStr += case when isnumeric(@PrevChar) = 0 and isnumeric(@CurrChar) = 0 then @AlphaStr
when isnumeric(@PrevChar) = 0 and isnumeric(@CurrChar) = 1 and isnumeric(@NextChar) = 1 and @CurrChar in ('.','+','-') then @NumericStr
when isnumeric(@PrevChar) = 1 and isnumeric(@CurrChar) = 1 and isnumeric(@NextChar) = 0 then REVERSE(@NumericStr) + @AlphaStr
when isnumeric(@PrevChar) = 1 and isnumeric(@CurrChar) = 0 and (isnumeric(@NextChar) = 0 or @NextChar in ('.','+','-')) then REVERSE(@NumericStr) + @AlphaStr
when isnumeric(@PrevChar) = 1 and isnumeric(@CurrChar) = 1 and @Index = @StrLen then REVERSE(@NumericStr) --> äâòðå ìñåó äîçøåæú
when isnumeric(@PrevChar) = 1 and isnumeric(@CurrChar) = 1 and @Index < @StrLen then ''
when isnumeric(@PrevChar) = 0 and isnumeric(@CurrChar) = 1 and /*@PrevChar<> ' ' and*/ (isnumeric(@NextChar) = 0 or @Index = @StrLen) then @NumericStr
when isnumeric(@CurrChar) = 0 then @AlphaStr
--when isnumeric(@PrevChar) = 0 and isnumeric(@CurrChar) = 1 and @PrevChar = ' ' and (isnumeric(@NextChar) = 0 or @Index = @StrLen) then @NumericStr
else ''
end
SET @index += 1;
set @NumericStr = case when isnumeric(@PrevChar) = 0 and isnumeric(@CurrChar) = 1 and isnumeric(@NextChar) = 1 and @CurrChar in ('.','+','-') then ''
else @NumericStr
end;
END
end
ELSE
BEGIN
SET @StrLen = len(ltrim(rtrim(@StrLeftToSpecial)));
SET @Str = @StrLeftToSpecial;
WHILE @Index <= @StrLen
BEGIN
SET @PrevChar = @CurrChar;
SET @CurrChar = substring(@Str,@Index,1)
SET @NextChar = substring(@Str,@Index+1,1)
SET @NumericStr = case when isnumeric(@CurrChar) = 0 then '' else @NumericStr end
SET @NumericStr += case when isnumeric(@CurrChar) = 1 then @CurrChar else '' end
SET @AlphaStr = case when isnumeric(@CurrChar) = 0 then @CurrChar else '' end
SET @ReturnLeft += case when isnumeric(@PrevChar) = 0 and isnumeric(@CurrChar) = 0 then @AlphaStr
when isnumeric(@PrevChar) = 0 and isnumeric(@CurrChar) = 1 and isnumeric(@NextChar) = 1 and @CurrChar in ('.','+','-') then @NumericStr
when isnumeric(@PrevChar) = 1 and isnumeric(@CurrChar) = 1 and isnumeric(@NextChar) = 0 then REVERSE(@NumericStr) + @AlphaStr
when isnumeric(@PrevChar) = 1 and isnumeric(@CurrChar) = 0 and (isnumeric(@NextChar) = 0 or @NextChar in ('.','+','-')) then REVERSE(@NumericStr) + @AlphaStr
when isnumeric(@PrevChar) = 1 and isnumeric(@CurrChar) = 1 and @Index = @StrLen then REVERSE(@NumericStr) --> äâòðå ìñåó äîçøåæú
when isnumeric(@PrevChar) = 1 and isnumeric(@CurrChar) = 1 and @Index < @StrLen then ''
when isnumeric(@PrevChar) = 0 and isnumeric(@CurrChar) = 1 and /*@PrevChar<> ' ' and*/ (isnumeric(@NextChar) = 0 or @Index = @StrLen) then @NumericStr
when isnumeric(@CurrChar) = 0 then @AlphaStr
--when isnumeric(@PrevChar) = 0 and isnumeric(@CurrChar) = 1 and @PrevChar = ' ' and (isnumeric(@NextChar) = 0 or @Index = @StrLen) then @NumericStr
else ''
end
SET @index += 1;
set @NumericStr = case when isnumeric(@PrevChar) = 0 and isnumeric(@CurrChar) = 1 and isnumeric(@NextChar) = 1 and @CurrChar in ('.','+','-') then ''
else @NumericStr
end;
END
SET @StrLen = len(ltrim(rtrim(@StrRightToSpecial)))+1;
SET @Str = @StrRightToSpecial;
SET @Index = 1
SET @NumericStr = ''
WHILE @Index <= @StrLen
BEGIN
SET @PrevChar = @CurrChar;
SET @CurrChar = substring(@Str,@Index,1)
SET @NextChar = substring(@Str,@Index+1,1)
SET @NumericStr = case when isnumeric(@CurrChar) = 0 then '' else @NumericStr end
SET @NumericStr += case when isnumeric(@CurrChar) = 1 then @CurrChar else '' end
SET @AlphaStr = case when isnumeric(@CurrChar) = 0 then @CurrChar else '' end
SET @ReturnRight += case when isnumeric(@PrevChar) = 0 and isnumeric(@CurrChar) = 0 then @AlphaStr
when isnumeric(@PrevChar) = 0 and isnumeric(@CurrChar) = 1 and isnumeric(@NextChar) = 1 and @CurrChar in ('.','+','-') then @NumericStr
when isnumeric(@PrevChar) = 1 and isnumeric(@CurrChar) = 1 and isnumeric(@NextChar) = 0 then REVERSE(@NumericStr) + @AlphaStr
when isnumeric(@PrevChar) = 1 and isnumeric(@CurrChar) = 0 and (isnumeric(@NextChar) = 0 or @NextChar in ('.','+','-')) then REVERSE(@NumericStr) + @AlphaStr
when isnumeric(@PrevChar) = 1 and isnumeric(@CurrChar) = 1 and @Index = @StrLen then REVERSE(@NumericStr) --> äâòðå ìñåó äîçøåæú
when isnumeric(@PrevChar) = 1 and isnumeric(@CurrChar) = 1 and @Index < @StrLen then ''
when isnumeric(@PrevChar) = 0 and isnumeric(@CurrChar) = 1 and /*@PrevChar<> ' ' and*/ (isnumeric(@NextChar) = 0 or @Index = @StrLen) then @NumericStr
when isnumeric(@CurrChar) = 0 then @AlphaStr
--when isnumeric(@PrevChar) = 0 and isnumeric(@CurrChar) = 1 and @PrevChar = ' ' and (isnumeric(@NextChar) = 0 or @Index = @StrLen) then @NumericStr
else ''
end
SET @index += 1;
set @NumericStr = case when isnumeric(@PrevChar) = 0 and isnumeric(@CurrChar) = 1 and isnumeric(@NextChar) = 1 and @CurrChar in ('.','+','-') then ''
else @NumericStr
end;
END
set @ReturnStr = @ReturnRight + '/' + ltrim(rtrim(@ReturnLeft))
END
return @ReturnStr
END