Search String........

[marydn]

Hello,

I need to find codes in a table. For example:

Code $ Amount
04134 1
4134 4
4134R 5
4134IM 6

I need to take these codes and make them consistent - 4134 and then total the amounts ($16) so I can link it to another table. Is there any way I can accomplish this in SQL 2K????
Any help would be greatly appreciated!

M.

 

[hneal98]

select left(code,4), sum(amount) from Tablename

 

[marydn]

Thanks, but then 04134 becomes 0413 and I want it to be 4134.

 

[marydn]

I get the desired result with this:

CASE WHEN Left(Code,1) = 0 THEN
Substring(Code,2,len(Code))
ELSE Left(Code,4)END AS Test

However, my numbers can sometimes be:

00964
964
964R

So you can see my problem.

 

[SQLSister]

First I would do this as a function. What you will want to do is check the individual charcters until you find the first non zero number, keeping count of the position in the string. Unfortunately this will involve a loop. Then you will use that number in a substring command to strip the zeros off. You can do a simliar thing to get the letters off the end, but if the main code is always a set number of characters, you won't need to.

Now as to how I would implement it, I would add a field to the table for the main code and then populate it at the time the trecord is entered or changed using a trigger. (you may be able to do this in a calculated field, too, I haven;t tried one using a function that I recall, it may be possible. Check it out in books on line.) Then I would query against it. The reason why I would do this, is that runnnig a complex calculation like this against many records would be very slow. Much better to strip out the data you need one record at a time. Of course you'll have to fix up all the existing data too.

 

[Golom]

How about the 'Val' function?

Val stops evaluation when it hits a non-numeric character so, as long as the non-numerics are always trailing the numerics, Val([FieldName]) should give 4134 for input values
[tt]
04134 OR
4134 OR
4134R OR
4134IM
[/tt]

 

[hneal98]

Another way would be like this:

select convert(integer,left(code,4)), sum(amount) from Tablename

 

[marydn]

Hmmmmm...I tried Val, but apparently it is not a valid SQL function. I also tried:

select convert(integer,left(code,4)), sum(amount) from Tablename

However, that takes my 04134 and makes it 413 instead of the 4134 I desire.

 

[sunila7]

Try this...

select Sum(amount)
From
(select case when patindex('%4134%',code) <> 0 then 1 else 0 end patternfound, amount from #t) TBL
where patternfound = 1
Group by patternfound


Sunil

 

[snag56]

SELECT CASE
WHEN ISNUMERIC(Code) = 0 THEN CONVERT(int,Left(Code,4))
ELSE CONVERT(int,Code)
END AS Test,
SUM(amount)
FROM test_search
GROUP BY CASE
WHEN ISNUMERIC(Code) = 0 THEN CONVERT(int,Left(Code,4))
ELSE CONVERT(int,Code)
END

 

[lyudmila]

I would create a new field CODE_new =

CASE LEN(ISNULL([A].

,''))
	  WHEN 0 THEN CAST('' AS CHAR(30))
	  ELSE
	    CASE WHEN PATINDEX('%[1-9]%', [A].[Code]) = 0 THEN CAST('' AS CHAR(30))
	    ELSE
		CAST
		(
		  CAST
		  (
		  REPLACE
		  (
	              CASE WHEN PATINDEX('[0-9]', SUBSTRING([A].[Code],1,1)) = 1 THEN SUBSTRING([A].[Code],1,1) ELSE '' END
	            + CASE WHEN PATINDEX('[0-9]', SUBSTRING([A].[Code],2,1)) = 1 THEN SUBSTRING([A].[Code],2,1) ELSE '' END
	            + CASE WHEN PATINDEX('[0-9]', SUBSTRING([A].[Code],3,1)) = 1 THEN SUBSTRING([A].[Code],3,1) ELSE '' END
	            + CASE WHEN PATINDEX('[0-9]', SUBSTRING([A].[Code],4,1)) = 1 THEN SUBSTRING([A].[Code],4,1) ELSE '' END
	            + CASE WHEN PATINDEX('[0-9]', SUBSTRING([A].[Code],5,1)) = 1 THEN SUBSTRING([A].[Code],5,1) ELSE '' END
	            + CASE WHEN PATINDEX('[0-9]', SUBSTRING([A].[Code],6,1)) = 1 THEN SUBSTRING([A].[Code],6,1) ELSE '' END
	            + CASE WHEN PATINDEX('[0-9]', SUBSTRING([A].[Code],7,1)) = 1 THEN SUBSTRING([A].[Code],7,1) ELSE '' END
	            + CASE WHEN PATINDEX('[0-9]', SUBSTRING([A].[Code],8,1)) = 1 THEN SUBSTRING([A].[Code],8,1) ELSE '' END
	            + CASE WHEN PATINDEX('[0-9]', SUBSTRING([A].[Code],9,1)) = 1 THEN SUBSTRING([A].[Code],9,1) ELSE '' END
	            + CASE WHEN PATINDEX('[0-9]', SUBSTRING([A].[Code],10,1)) = 1 THEN SUBSTRING([A].[Code],10,1) ELSE '' END
	             ,' ',''
	          ) AS NUMERIC(28,0))
		AS CHAR(30))
	    END	
	  END  ---                                           Adjust this code to you field size
and then group by this new field and calculate SUM(ATM)

 

[hneal98]

Just change the digit.

select convert(integer,left(code,5)), sum(amount) from Tablename