February 1, 2006 at 9:44 pm
Is there a way to declare a column name as a variable? Maybe you can't do this but here is an example of what I am trying to do:
DECLARE @FIELDNAME CHAR(50),
@NEWPHONE CHAR(50)
SET @FIELDNAME = 'phone'
SET @NEWPHONE = SUBSTRING(@FIELDNAME,2,3)+SUBSTRING(@FIELDNAME,5,3)+SUBSTRING(@FIELDNAME,9,10)
SELECT @FIELDNAME,
@NEWPHONE
FROM cn_phone
WHERE @FIELDNAME like '(%)%-%'
OR @FIELDNAME like '%-%-%'
February 2, 2006 at 8:53 am
Dynamic SQL...
DECLARE @FIELDNAME VARCHAR(50), @NEWPHONE VARCHAR(50), @SQL VARCHAR(1000)
SET @FIELDNAME = 'phone'
SET @NEWPHONE = SUBSTRING(@FIELDNAME,2,3)+SUBSTRING(@FIELDNAME,5,3)+SUBSTRING(@FIELDNAME,9,10)
SET @SQL = 'SELECT ' + @FIELDNAME + ', ' + @NEWPHONE
SET @SQL = @SQL + ' FROM cn_phone '
SET @SQL = @SQL + ' WHERE ' + @FIELDNAME + ' LIKE ''(%)%-%'''
SET @SQL = @SQL + ' OR ' + @FIELDNAME + ' LIKE ''%-%-%'''
EXEC (@SQL)
February 2, 2006 at 11:28 am
The problem I have now is that it is looking at the variable @FIELDNAME when setting the @NEWPHONE as just a string rather than a field in the database.
I've changed that so it has the theh full name, cn_phone.phone but then it says that it does not match with a table name or alias name used in the query.
February 2, 2006 at 11:34 am
Just an idea, what if you use syscolumns to set those column variables? Could this help?
Becasue at the time the declare statement is being created, you are right on that, the @FieldName to be found is a string, if you use syscolumns that might help to identify the columns you are trying to use.
Again, is just an idea and hope it helps
February 2, 2006 at 11:43 am
..or using [Information_Schema] views may be more apt...You'll save yourself a headache in migrating to SQL Server 2005.
Mathew J Kulangara
sqladventures.blogspot.com
February 2, 2006 at 12:23 pm
I think I understand your issue with @NEWPHONE name, however SET @NEWPHONE = SUBSTRING(@FIELDNAME,2,3)+SUBSTRING(@FIELDNAME,5,3)+SUBSTRING(@FIELDNAME,9,10) is always going to set a value based on the value of @FIELDNAME.
How about...
DECLARE @FIELDNAME VARCHAR(50), @SQL VARCHAR(1000)
SET @FIELDNAME = 'phone'
SET @SQL = 'SELECT ' + @FIELDNAME + ', SUBSTRING(' + @FIELDNAME + ', 2, 3) + '
SET @SQL = @SQL + 'SUBSTRING(' + @FIELDNAME + ', 5, 3) + SUBSTRING(' + @FIELDNAME + ', 9, 10) '
SET @SQL = @SQL + ' FROM cn_phone '
SET @SQL = @SQL + ' WHERE ' + @FIELDNAME + ' LIKE ''(%)%-%'''
SET @SQL = @SQL + ' OR ' + @FIELDNAME + ' LIKE ''%-%-%'''
EXEC (@SQL)
February 2, 2006 at 12:33 pm
Of course %-%-% is different from (%)%-% so....
DECLARE @FIELDNAME VARCHAR(50), @SQL VARCHAR(1000)
SET @FIELDNAME = 'phone'
SET @SQL = 'SELECT ' + @FIELDNAME + ', CASE LEFT(' + @FIELDNAME + ', 1) WHEN ''('' THEN SUBSTRING(' + @FIELDNAME + ', 2, 3) '
SET @SQL = @SQL + 'ELSE LEFT(' + @FIELDNAME + ', 3) END + '
SET @SQL = @SQL + 'CASE LEFT(' + @FIELDNAME + ', 1) WHEN ''('' THEN SUBSTRING(' + @FIELDNAME + ', 6, 3) '
SET @SQL = @SQL + 'ELSE SUBSTRING(' + @FIELDNAME + ', 5, 3) END + '
SET @SQL = @SQL + 'RIGHT(' + @FIELDNAME + ', 4)'
SET @SQL = @SQL + ' FROM cn_phone '
SET @SQL = @SQL + ' WHERE ' + @FIELDNAME + ' LIKE ''(%)%-%'''
SET @SQL = @SQL + ' OR ' + @FIELDNAME + ' LIKE ''%-%-%'''
EXEC (@SQL)
February 2, 2006 at 2:37 pm
Thanks Joe. Your latest suggestions occurred to me while stuck in a long meeting this afternoon. I did not, however think of your final solution for handeling both variations at once. Thanks for all your help. I also used this as a basis for passing an update query in the database to change these fields where the mask characters are present.
Thanks again.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply