[thelist] SQL Server: How Can I Convert VARCHAR to INT?

Rory.Plaire at wahchang.com Rory.Plaire at wahchang.com
Thu Aug 16 12:48:04 CDT 2001


| Looks like a job for regular expressions which, afaik, 
| SQL Server

Well.... 8)

There is the PATINDEX function which, combined with the VBesque Left() and
Right(), can provide regexp type matching... There is also Replace() and
Substring() for even more matching and replacement.

You can use something like this with an UPDATE as well, selecting the first
part, if it is a number, and then updating it. You roll the whole thing into
an UPDATE and potentially clean the whole table this way.

<possibility>

UPDATE table SET field = LEFT(field, PATINDEX('% %', field)) WHERE criteria

</possibility>

<tip type="SQL Server RexExp" warning="somewhat convoluted for an example!">

MS SQL Server lacks Regular Expressions. This is generally not noticed if
you are strictly MS and have blocked yourself from using versions of VB
later than 4. Since then, Regular Expressions have even pervaded the MS
Weltanschauung... except SQL Server.

Here is an example for taking a comma delimited list and parsing the
elements to put them in a table. This example can take lists which have
either 2 or 3 elements per group, as defined by the local variable
@Attr_Type_Inc. The list is in @attr_list.

------------------
WHILE PatIndex('%,%', @attr_list) <> 0
BEGIN
	SET @name = Left(@attr_list, PatIndex('%,%', @attr_list)-1)
	SET @attr_list = LTrim(Right(@attr_list, Len(@attr_list) -
PatIndex('%,%', @attr_list)))
	IF(PatIndex('%,%', @attr_list) = 0)
		SET @value = Left(@attr_list, Len(@attr_list))
	ELSE
		SET @value = Left(@attr_list, PatIndex('%,%', @attr_list)-1)
	SET @attr_list= LTrim(Right(@attr_list, Len(@attr_list) -
PatIndex('%,%', @attr_list)))
	IF @Attr_Type_Inc > 0
	BEGIN
		IF (PatIndex('%,%', @attr_list) = 0)
			SET @type = Left(@attr_list, Len(@attr_list))
		ELSE
			SET @type = Left(@attr_list, PatIndex('%,%',
@attr_list)-1)
		SET @attr_list= LTrim(Right(@attr_list, Len(@attr_list) -
PatIndex('%,%', @attr_list)))
	END
	ELSE
		SET @type = NULL
	INSERT INTO tblProfile_Attributes(Profile_Id, Attribute_Name,
Attribute_Value, Attribute_Type)
		VALUES(@userid, @name, @value, @type)
END
--------------------
</tip>


<rory disposition="so thankful that the demo meeting went well and I get to
come back and reply to evolt!" alt="8)"/>




More information about the thelist mailing list