Can't contain non-alphabetic chars

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

General SQL Server Forums

New to SQL Server Programming

Can't contain non-alphabetic chars

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-06-11 : 23:59:39

With this procedure,can't show if GiftTitle contains space ,nonalphabetic chars(&,-,..) and if start with numnber.
How can i modify ??
Plz help me.
Thanks.

CREATE PROCEDURE dbo.trymatrix AS
DECLARE @SQLCommand varchar(4096), @column sysname

SET @SQLCommand = 'select c.CustomerID as Cust_ID,Name as Co_Name, ' + char(13)

DECLARE curGift CURSOR STATIC LOCAL FOR

select distinct GiftTitle from Redemption1

OPEN curGift

WHILE 1 = 1

BEGIN

FETCH curGift INTO @column

IF @@FETCH_STATUS <> 0

BREAK

SET @SQLCommand = @SQLCommand +

'sum(case GiftTitle when ''' +@column +''' then 1 else null end) as '+ @column + ','+char(13)

END

DEALLOCATE curGift

SET @SQLCommand = left (@SQLcommand, len(@SQLcommand)-2) + char(13)

SET @SQLCommand = @SQLCommand +

'from Customer c

inner join Redemption1 d

on c.CustomerID=d.CustomerID

group by Name,c.CustomerID

order by c.CustomerID'

PRINT @SQLCommand

EXEC (@SQLCommand)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-12 : 00:48:36

WHERE SomeCol NOT LIKE '%[^a-z]%'

E 12°55'05.25"
N 56°04'39.16"

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-06-12 : 01:25:11

Thanks.
Now i changed to like:
SET @SQLCommand = @SQLCommand +

'sum(case GiftTitle when ''' +@column +''' then 1 else null end) as ['+ replace(replace(@column, '[', '_'), ']', '_') + '],'+char(13)

quote:
Originally posted by Peso

WHERE SomeCol NOT LIKE '%[^a-z]%'

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-12 : 02:22:42

Or more simpler

sum(case GiftTitle when ' + quotename(@column, '''') + ' then 1 else null end) as ' + quotename(@column) + ',' + char(13)

This is also take care of single quote character " ' " and ending bracket " ] ".

E 12°55'05.25"
N 56°04'39.16"

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-06-12 : 04:24:54

Great!
Now i changed mine with yours.

Thanks Peso.

quote:
Originally posted by Peso

Or more simpler

sum(case GiftTitle when ' + quotename(@column, '''') + ' then 1 else null end) as ' + quotename(@column) + ',' + char(13)

This is also take care of single quote character " ' " and ending bracket " ] ".

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources