Count Commas in field
-
I need a simple, quick way to count the number of Commas (',') in a column.
(It would be nice if I could specify the character to count.)
I have to convert a column of addresses that may be comma delimeted or not into their component parts (Address, City, State, Zip).
Joy.
(If you have a solution already, that would be nice, too. I hate re-inventing the wheel.)
The addresses are "mostly" in the US.
Bryan Clauss
-
count >>
Select Len('some,commas,') - len(replace('some,commas,',',','')) as NumberOfCommas
Maybe this split function can be of some use :
IF Object_id('fnSplit_Set') > 0
DROP FUNCTION dbo.fnSplit_Set
GO
IF Object_id('Numbers') > 0
DROP TABLE dbo.Numbers
GO
CREATE TABLE dbo.Numbers (PkNumber int identity(1,1), dude bit null, CONSTRAINT Pk_Number PRIMARY KEY CLUSTERED (PkNumber))
GO
INSERT INTO dbo.Numbers (dude)
SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80
GO
ALTER TABLE dbo.Numbers
DROP COLUMN dude
GO
--Select min(PkNumber) as MinA, max(PkNumber) as MaxA from dbo.Numbers
--1, 8000
GO
CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
Return
Select dtSplitted.EachID, dtSplitted.Rank from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID
, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank
FROM dbo.Numbers N
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber 0
GO
DECLARE @Ids as varchar(8000)
SET @IDS = ''
Select @IDS = @IDS + CAST(id as varchar(10)) + ',' from (Select TOP 10 id from dbo.SysObjects order by NEWID()) dtIds
SET @IDS = left(@IDS, ABS(len(@IDS) - 1))
PRINT @IDS
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by O.Name
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by dtIDS.Rank
--Dont't ever forget to cast the eachid column to the same datatype as in the join to avoid table scans.
-
What's wrong with this??
Select Len('some,commas,') - len(replace('some,commas,',',','')) as NumberOfCommas
-
Simple enough. Thanks!
Remi's solution is a little larger than I need right now. (Besides, I have a function that will return a Text field as rows broken by a character.)
-
Select Len('some,commas,') - len(replace('some,commas,',',','')) as NumberOfCommas is large????????
Select OneField from dbo.Split('String', 'separator') is large?????
-
Sorry, Remi.
I don't want to Byte the Hand that feeds me.
When I said that your solution was a little more than I needed at the moment, I was refering to your "Split" function.
I just used your "Select Len('some,commas,') - len(replace('some,commas,',',','')) as NumberOfCommas" line, and that is what I need right now to start rolling on analyzing my DataBase Gods Awful user entered data that I have to parse out.
You have been a big help to me in the past (see, I am trying to butter you up here...), and I did not mean to offend you.
Keep the help coming!
Thanks again
Bryan
-
Sorry... I've just seen one too many users who ditch the solution before even trying it... Glad you're not one of them.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply