Counting occurrences of character within string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Counting occurrences of character within string

Author

Topic

VPeters
Starting Member

20 Posts

Posted - 2007-03-06 : 10:05:48

Hello,

I am hoping someone can help me out. I need to figure out the best way to count the number of times a '-' appears within a string. The field is data type nvarchar(255). I need to be able to break the field apart based on the locations of the dash(es). The problem is, though, that one record could be xxxx-x-xx, one could be xx-xxxx, one could be xxxx, one could be xxxx-xxxx-xxxx, etc. To date, there are no more than two dashes in the field, but going forward there could be. Also, there is no consistency to how many characters are before/after each dash. I attempted to create a user-defined function to get a count of the dashes; however, I cannot return more than one value with it. Do I need to create a loop, or is there an easier way to do this?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-06 : 10:07:33

[code]len(@str) - len(replace(@str, '-', ''))[/code]

VPeters
Starting Member

20 Posts

Posted - 2007-03-06 : 10:13:40

Thank you so much! I was definitly over-complicating it!

VPeters
Starting Member

20 Posts

Posted - 2007-03-06 : 10:32:33

Okay, next question if you can help! Now that I've determined whether there are no, one, or two dashes in a field, how do I pull the characters in between if there are two dashes? Getting the characters to the left of the first dash is easy, and I seem to be able to pull the characters to the right of the last dash, be it one or two dashes. But when there are two dashes, I cannot seem to capture the data in the middle. Any thoughts on that? Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 11:26:48

With the help of this very fast string parser here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

declare @s varchar(200)

set @s = 'Company Name-ZAR 1795000000-21 February 2030-FRN-bond'

select	@s,
	dbo.fnParseString(3, '-', @s)

Peter Larsson
Helsingborg, Sweden

VPeters
Starting Member

20 Posts

Posted - 2007-03-06 : 12:06:02

I'm sorry - I am very unfamiliar with user-defined functions. How would I use this function in my code?

The field I'm evaluating is called InventoryKey. I have chosen 4 records to work with for testing. The values are: 0, 10353-10360, 1036, and 10361-10664-10669.

Below is my code thus far. The THEN statements with a ? as the result are the ones of which I am unclear.

SELECT DISTINCT
OrigKey = bpi.InventoryKey,
-- Values to left of 1st dash
Opt1 =
CASE
WHEN LEN(bpi.InventoryKey) - LEN(REPLACE(bpi.InventoryKey,
'-', '')) > 0
THEN LEFT(bpi.InventoryKey, CHARINDEX('-',bpi.InventoryKey)
- 1)
ELSE bpi.InventoryKey
END,
-- Values after 1st dash or between 1st & 2nd dashes
Opt2 =
CASE
WHEN LEN(bpi.InventoryKey) - LEN(REPLACE(bpi.InventoryKey,
'-', '')) = 1
THEN SUBSTRING(bpi.InventoryKey, CHARINDEX('-',
bpi.InventoryKey) + 1, LEN(bpi.InventoryKey) -
CHARINDEX('-', bpi.InventoryKey) + 1)
WHEN LEN(bpi.InventoryKey) - LEN(REPLACE(bpi.InventoryKey,
'-', '')) = 2
THEN '?'
ELSE '0'
END,
-- Values to right of 2nd dash, if exists
Opt3 =
CASE
WHEN LEN(bpi.InventoryKey) - LEN(REPLACE(bpi.InventoryKey,
'-', '')) > 1
THEN '?'
ELSE '0'
END
FROM dbo.BVC_ProductInventory AS bpi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources