How to split a string

Question

How do I convert the following c# to ms sql?

#region determine if Dimension has length & width
decimal _dimension = 0;
string[] xy = new string[1];
Dimension = Dimension.ToUpper();
if (Dimension.Contains('X'))
{
    xy = Dimension.Split('X');
    _dimension = (Convert.ToInt32(xy[0]) * Convert.ToInt32(xy[1]) / 144);
}
else
{
        _dimension = Convert.ToDecimal(Dimension);
}
#endregion


DECLARE @_dimension numeric
select @_dimension = Dimension

how to upcase string?

how to split string at 'X'?

Have you tried anything yet? Do you have SQL that isn't working as intended? Otherwise, all this says to me is that you're too lazy to attempt it yourself and want us to figure it out for you. — George Johnston, Jul 20 '11 at 14:46
Questions that don't demonstrate some effort on your part tend to cause negative reactions from our community. In the future, please post your attempt, or at least explain why you're stuck at the beginning. — Tim Post, Jul 21 '11 at 00:51

score 1 · Answer 1 · edited Jul 20 '11 at 14:50

The .ToUpper you use here is replaced by the T-SQL function UPPER(), and the .Contains can be replaced with something like CHARINDEX('X', YourString) to return the location of "X" - if it's greater than zero, then it's the place in the string where it appears. There's no equivalent for the .Split() function per se, but you could do a CHARINDEX() to find the first "X", then to do another to find the next "X", and then take the difference and do a SUBSTRING() to get just that smaller string from your larger input.

Does that make sense?

score 0 · Answer 2 · edited May 23 '17 at 10:34

0

String splitting tutorial: http://sqltutorials.blogspot.com/2007/09/sql-function-split.html
Another SO question: How do I split a string so I can access item x?
Alternative implementation: http://tim.mackey.ie/SQLStringSplitFunction.aspx

edited May 23 '17 at 10:34

Community

1
1

answered Jul 20 '11 at 14:50

xan

7,116
8
41
64

score 0 · Answer 3 · answered Jul 20 '11 at 14:53

You can;

select 
    case when (@_dimension like '%x%') then 
        (cast(left(@_dimension, charindex('X', @_dimension, 1) - 1) as int)
        *
        cast(substring(@_dimension, 1 + charindex('X', @_dimension, 1), len(@_dimension)) as int)) / 144.00
    else
        cast(@_dimension as decimal(10, 5))
    end

score 0 · Answer 4 · answered Jul 20 '11 at 14:54

for splitting you need to create your own T-SQL function as the one showed below:

CREATE FUNCTION SPLIT ( @str_in VARCHAR(8000), @separator VARCHAR(4) ) RETURNS @strtable TABLE (strval VARCHAR(8000)) AS BEGIN

DECLARE @Occurrences INT, @Counter INT, @tmpStr VARCHAR(8000)

SET @Counter = 0 IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator SET @str_in = @str_in + @separator

SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator) SET @tmpStr = @str_in

WHILE @Counter <= @Occurrences BEGIN SET @Counter = @Counter + 1 INSERT INTO @strtable VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))

SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)

IF DATALENGTH(@tmpStr) = 0 BREAK

END RETURN END

How to split a string

4 Answers4