Re: Help with String Functions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Itzik Ben-Gan (itzik_at_REMOVETHIS.SolidQualityLearning.com)
Date: 03/07/04 

Date: Sun, 7 Mar 2004 22:39:55 +0100

Khurram,

You can create a function that splits the array into elements using an
auxiliary table of numbers:

-- Populate an auxiliary table of numbers
IF OBJECT_ID('Nums') IS NOT NULL
  DROP TABLE Nums
GO

CREATE TABLE Nums(n INT NOT NULL PRIMARY KEY)
GO

SET NOCOUNT ON

DECLARE @max AS INT, @rc AS INT

SET @max = 8000
SET @rc = 1

BEGIN TRAN
  INSERT INTO Nums VALUES(1)

  WHILE @rc * 2 <= @max
  BEGIN
    INSERT INTO Nums
      SELECT n + @rc FROM Nums

    SET @rc = @rc * 2
  END

  INSERT INTO Nums
    SELECT n + @rc FROM Nums
    WHERE n + @rc <= @max
COMMIT TRAN

-- Function that splits an array into elements
IF OBJECT_ID('fn_splitarr') IS NOT NULL
  DROP FUNCTION fn_splitarr
GO

CREATE FUNCTION fn_splitarr(@arr AS VARCHAR(8000))
  RETURNS TABLE
AS
RETURN
  SELECT
    n - LEN(REPLACE(LEFT(string, n), ',', '')) + 1 AS pos,
    CAST(SUBSTRING(string, n,
          CHARINDEX(',', string + ',', n) - n)
        AS INT) AS val
  FROM (SELECT @arr AS string) AS Arrays JOIN Nums
    ON n <= LEN(string)
    AND SUBSTRING(',' + string, n, 1) = ','
GO

-- Test function
SELECT * FROM fn_splitarr('7,30,90,180')
pos val
----------- -----------
1 7
2 30
3 90
4 180 

-- 
BG, SQL Server MVP
www.SolidQualityLearning.com
"Khurram Chaudhary" <chaudharykhurram@hotmail.com> wrote in message
news:ORKfOdHBEHA.2632@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> I want to be able to get only the numbers in a comma-delimited string. How
> can I do that? For example, I have the string '7,30,90,180'. How do only
get
> the one number at at time?
>
> Thanks.
>
> Khurram
>
>

Relevant Pages

  • Re: Need help with string splitter.
    ... > I'm working on a program which splits a long string into a series of ... The program prints out the string again by printing out each ... > occupied segment of the array, ... and it stops copying data into the array after the first ...
    (comp.lang.cpp)
  • Need help with string splitter.
    ... I'm working on a program which splits a long string into a series of ... You enter a string into variable "str" ... occupied segment of the array, ... I think it might be a problem with the While loop, ...
    (comp.lang.cpp)
  • Re: Writing a service Q: how to get the parameter from...
    ... It seems the args come through as an array however when the array is created ... If it splits on more elements it seems to make ... and concatonate string so that I can split it the way I want. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: splitting a string (and memory allocation)
    ... > I'm trying to write a function which splits a string (possibly multiple ... > times) on a particular character and returns the strings which has been ... that was replaced to an array which is returned to the calling function. ...
    (comp.lang.c)
  • Re: please debug this generic program
    ... return sum; ... public static void main{ ... `nums' is an array, and an array has no method ...
    (comp.lang.java.programmer)