cheryl9063
asked on
SQL Get number of commas in a string
I have a variable called @location that could have data like: 5222 michaux rd, GSO, NC,27410.. I want to know how many commas are in it..answer is 3 of course..Should I use patindex?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Lowfatspread - Not sure on why did you tell that won't work.
I tried ewangoya's query for string 'a,,,' and got the count as 3.
I tried ewangoya's query for string 'a,,,' and got the count as 3.
'a , , ,'
try it with spaces between the comma at the end of the string.....
LEN ignores spaces so if you remove the last/trailing comma you reduce the reported LEN() value by more than 1....
use datalength() for consistency and cater for bytes/characters depending on the datatype.
try it with spaces between the comma at the end of the string.....
LEN ignores spaces so if you remove the last/trailing comma you reduce the reported LEN() value by more than 1....
use datalength() for consistency and cater for bytes/characters depending on the datatype.
ASKER
Thanks.. this will ALWAYS be an address so some different scenerios would be like
5222 michaux rd GSO, NC,27410
or
5222 michaux rd GSO NC 27410
5222 michaux rd GSO, NC,27410
or
5222 michaux rd GSO NC 27410
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The real point of the query above is to parse the delimited string into a table, but using count you can also derive the comma count.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
SELECT @count = LEN(@string) - LEN(REPLACE(@string, ',', ''))
e.g consider string 'a, , ,'
replacing the commas with space / emopty string would lead LEN() to report it as length 1 not the 3 you were hoping for
you could use DATALENGTH and replace with an empty string
if Varchar
SELECT @count = DATALENGTH(@string) - DATALENGTH(REPLACE(@string
if NVarchar
SELECT @count = (DATALENGTH(@string) - DATALENGTH(REPLACE(@string